#!/home/python/.virtualenvs/open/bin/python

import sys
sys.path.append('./')
sys.path.insert(0, "../")

import os
if not os.getenv('DJANGO_SETTINGS_MODULE'):
    os.environ['DJANGO_SETTINGS_MODULE'] = 'equity_mall.settings.dev'

import django, socket
django.setup()

from celery import shared_task
import requests, datetime, json, time, xlrd, hashlib, logging, base64, pymysql, re
from django_redis import get_redis_connection
from django.conf import settings
from django.db import transaction
from random import randint, shuffle
import Crypto.Signature.PKCS1_v1_5 as sign_PKCS1_v1_5
from Crypto.PublicKey import RSA
from Crypto.Hash import SHA256
from decimal import Decimal
from django.db.models import Q, F, Func, Max, Sum
from django.db.models.expressions import RawSQL

from goods.models import GoodsModifyApplyment
from equity_mall.utils.refresh_effective_cookie import update_gzh_accesstoken
from activities.models import Channel
from vouchers.models import UpdateStockUsableMerchant, AliDiscountCoupon, AliDiscountCouponMerchant, BlackTable, AliDiscountCouponSyncWYHRecord, AliDiscountCouponLevel, AliDiscountCouponLevelRelation, AliDiscountCouponInfo, AliDiscountCouponWYHRelevance, AliDiscountCouponMerchantTableHistory, AliDiscountCouponMerchantTable
from merchants.models import *
from users.models import Level, OldWYHLevel, User, CustomerUser
from equity_mall.utils.sync_img_to_local import sync_img_to_local, make_qrcode, make_qrcode2
from equity_mall.utils.wash_level1 import check_file
from equity_mall.utils.utils import random_str, get_level_distribution, get_wyhactivity_data, get_wyhactivity_relevance, get_wyhactivity_csvdata, update_wyhactivity_data_level, get_wyhactivity_status, get_ylx_level_distribution, get_ylxactivity_data, get_ylxactivity_data_status, get_ylxactivity_publicity_data
from equity_mall.utils.synchronization_kl_order import synchronization_kl_order, synchronization_kl_merchant
from merchants.utils import query_transettle, query_sxf_transettle
from equity_mall.utils.qiniu_storage import pic_upload
from equity_mall.utils.synchronization_kl_order import write_excel_xlsx
from orders.models import ExpressQuest, OrderInfo, ExpressType, Express, ExpressOrderRelevance, OrderAdvance, AfterSaleApplicationForm, OrderGoods
from equity_mall.utils.sync_img_to_local import make_qrcode5
from alipaynfc.models import NFCDeviceBountyQuest, NFCDeviceBountyQuestSubscription

logger = logging.getLogger('django')


@shared_task
def expedite_applyment_send(data):
    '''小程序成功模板推送'''
    redis_conn = get_redis_connection("small_token")
    access_token = redis_conn.get(settings.MANAGER_APP_ID)
    if not access_token:
        small_url = f'https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={settings.MANAGER_APP_ID}&secret={settings.MANAGER_APP_SECRET}'
        small_reaponse = requests.get(small_url)
        small_data = small_reaponse.json()
        access_token = small_data['access_token']
        redis_conn.setex(settings.MANAGER_APP_ID, small_data['expires_in'], access_token)
    else:
        access_token = access_token.decode()
    applyment = GoodsModifyApplyment.objects.get(id=data['id'])
    companyuser = applyment.companyuser
    if companyuser.openid:
        if applyment.applyment_status == 3:
            phrase2 = '审核驳回'
            thing3 = applyment.reject_reason
        else:
            phrase2 = '审核通过'
            thing3 = '恭喜您，该商品已审核通过，可以上架售卖。'
        small_body = {
            "touser": companyuser.openid,
            "template_id": "E9qj2WVauK5CVDAMRE3GcogzWyhelk80-APySZGKufo",
            "page": f"/pages/pro_detail/pro_detail?id={applyment.goods_id}",
            "miniprogram_state": "formal",
            "lang": "zh_CN",
            "data": {
                "thing9": {
                    "value": applyment.get_applyment_type_display()
                },
                "phrase2": {
                    "value": phrase2
                },
                "thing3": {
                    "value": thing3
                },
                "time22": {
                    "value": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                }
            }
        }
        small_url1 = f'https://api.weixin.qq.com/cgi-bin/message/subscribe/send?access_token={access_token}'
        response = requests.post(small_url1, data=json.dumps(small_body), headers={'Content-Type': 'application/json'})
        return '已推送'
    return '无绑定openid'


@shared_task
def aftersale_applyment_send(data):
    '''小程序成功模板推送'''
    redis_conn = get_redis_connection("small_token")
    access_token = redis_conn.get(settings.CUSTOMER_APP_ID_WYH)
    if not access_token:
        small_url = f'https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={settings.CUSTOMER_APP_ID_WYH}&secret={settings.CUSTOMER_APP_SECRET_WYH}'
        small_reaponse = requests.get(small_url)
        small_data = small_reaponse.json()
        access_token = small_data['access_token']
        redis_conn.setex(settings.CUSTOMER_APP_ID_WYH, small_data['expires_in'], access_token)
    else:
        access_token = access_token.decode()
    applyment = AfterSaleApplicationForm.objects.get(id=data['id'])
    customeruservice = applyment.customeruservice
    if customeruservice.wyh_openid:
        if applyment.form_status == 3:
            phrase5 = '不通过'
        else:
            phrase5 = '通过'
        small_body = {
            "touser": customeruservice.wyh_openid,
            "template_id": "nil5vPIJu-Up7-xRBgIs-SvMNISZmz9K0KdBUErjgIo",
            "page": f"/pages/order_detail/order_detail?id={applyment.order_id}",
            "miniprogram_state": "formal",
            "lang": "zh_CN",
            "data": {
                "phrase5": {
                    "value": phrase5
                },
                "amount3": {
                    "value": f'{applyment.refund_money}元'
                },
                "character_string1": {
                    "value": applyment.application_num
                },
                "time4": {
                    "value": applyment.create_time.strftime("%Y-%m-%d %H:%M:%S")
                }
            }
        }
        small_url1 = f'https://api.weixin.qq.com/cgi-bin/message/subscribe/send?access_token={access_token}'
        response = requests.post(small_url1, data=json.dumps(small_body), headers={'Content-Type': 'application/json'})
        return '已推送'
    return '无绑定openid'


@shared_task
def express_send(data):
    '''小程序成功模板推送'''
    redis_conn = get_redis_connection("small_token")
    access_token = redis_conn.get(settings.CUSTOMER_APP_ID_WYH)
    if not access_token:
        small_url = f'https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={settings.CUSTOMER_APP_ID_WYH}&secret={settings.CUSTOMER_APP_SECRET_WYH}'
        small_reaponse = requests.get(small_url)
        small_data = small_reaponse.json()
        access_token = small_data['access_token']
        redis_conn.setex(settings.CUSTOMER_APP_ID_WYH, small_data['expires_in'], access_token)
    else:
        access_token = access_token.decode()
    order = OrderInfo.objects.get(id=data['id'])
    express = Express.objects.get(id=data['express_id'])
    customeruservice = order.customeruservice
    if customeruservice and customeruservice.wyh_openid:
        goods_name = OrderGoods.objects.filter(order_id=order.id).first().goods.name
        if len(goods_name) > 14:
            goods_name = f'{goods_name[:14]}……'
        small_body = {
            "touser": customeruservice.wyh_openid,
            "template_id": "9-TJ_wIjJiyUh0M-WKiEIcf-fQJUyKh4xXtuyZ29iVc",
            "page": f"/pages/order_detail/order_detail?id={order.id}",
            "miniprogram_state": "formal",
            "lang": "zh_CN",
            "data": {
                "character_string3": {
                    "value": order.orderNum
                },
                "thing2": {
                    "value": goods_name
                },
                "thing1": {
                    "value": express.express_type.express_type_name
                },
                "character_string5": {
                    "value": express.express_number
                },
                "date4": {
                    "value": datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                }
            }
        }
        small_url1 = f'https://api.weixin.qq.com/cgi-bin/message/subscribe/send?access_token={access_token}'
        response = requests.post(small_url1, data=json.dumps(small_body), headers={'Content-Type': 'application/json'})
        return '已推送'
    return '无绑定openid'


@shared_task
def update_ali_cookie(data):
    print('%s: update_ali_cookie' % time.ctime())
    redis_conn1 = get_redis_connection("gzh_accesstoken")
    gzh_accesstoken = redis_conn1.get("gzh_accesstoken")
    if not gzh_accesstoken:
        access_token = update_gzh_accesstoken()
    else:
        access_token = gzh_accesstoken.decode()
    headers = {"Content-Type": "application/json"}
    id1 = data['id']
    try:
        url = data['url']
        response = requests.get(url)
    except:
        data = {
            "touser": "oyNYvw0oN6GX0F4gvbEjbNjrLmJg",
            "template_id": "XB30WUBqXZ7QoZT4mqB331NC_gAUPOguJytU0ClM288",
            "data": {
                "first": {
                    "value": '跟新token失败!',
                    "color": "#ed6f56"
                },
                "keyword1": {
                    "value": '失败',
                    "color": "#878787"
                },
                "keyword2": {
                    "value": '远程服务器出问题',
                    "color": "#878787"
                },
                "remark": {
                    "value": '请人工刷新token,并检查远程服务器!',
                    "color": "#ed6f56"
                }
            }
        }
        response = requests.post(
            url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
            data=json.dumps(data), headers=headers)
        if response.json()['errcode'] != 0:
            access_token = update_gzh_accesstoken()
            response = requests.post(
                url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
                data=json.dumps(data), headers=headers)
        print('更新失败!')
        return None
    if response.status_code == 200:
        data = response.json()
        cookie = data['cookie']
        sc = Channel.objects.get(id=id1)
        sc.cookie = cookie
        sc.save()
        data = {
            "touser": "oyNYvw0oN6GX0F4gvbEjbNjrLmJg",
            "template_id": "XB30WUBqXZ7QoZT4mqB331NC_gAUPOguJytU0ClM288",
            "data": {
                "first": {
                    "value": '跟新token成功!',
                    "color": "#ed6f56"
                },
                "keyword1": {
                    "value": '成功',
                    "color": "#878787"
                },
                "keyword2": {
                    "value": '棒棒哒!',
                    "color": "#878787"
                },
                "remark": {
                    "value": '!',
                    "color": "#ed6f56"
                }
            }
        }
        response = requests.post(
            url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
            data=json.dumps(data), headers=headers)
        if response.json()['errcode'] != 0:
            access_token = update_gzh_accesstoken()
            response = requests.post(
                url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
                data=json.dumps(data), headers=headers)
        print('更新成功!')
    else:
        data = {
            "touser": "oyNYvw0oN6GX0F4gvbEjbNjrLmJg",
            "template_id": "XB30WUBqXZ7QoZT4mqB331NC_gAUPOguJytU0ClM288",
            "data": {
                "first": {
                    "value": '跟新token失败!',
                    "color": "#ed6f56"
                },
                "keyword1": {
                    "value": '失败',
                    "color": "#878787"
                },
                "keyword2": {
                    "value": '远程服务器出问题',
                    "color": "#878787"
                },
                "remark": {
                    "value": '请人工刷新token,并检查远程服务器!',
                    "color": "#ed6f56"
                }
            }
        }
        response = requests.post(
            url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
            data=json.dumps(data), headers=headers)
        if response.json()['errcode'] != 0:
            access_token = update_gzh_accesstoken()
            response = requests.post(
                url=f'https://api.weixin.qq.com/cgi-bin/message/template/send?access_token={access_token}',
                data=json.dumps(data), headers=headers)
        print('更新失败!')


@shared_task
def update_stock_usable_merchant(data):
    id = data['id']
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        update_stock_usable_merchant.delay({'id': id})
        return '移交其他服务器处理'
    history = UpdateStockUsableMerchant.objects.get(id=id)
    user = history.operation_user
    excel_data = []
    excel_data_title = ['批次号', '商户号', '业务单号']
    excel_data.append(excel_data_title)
    redis_conn = get_redis_connection("cache")
    black_sub_mchid_list = list(AliDiscountCouponMerchantTable.objects.exclude(is_white=1).values_list('sub_mchid', flat=True))
    try:
        if history.add_type == 1:
            file_contents = requests.get(history.file.url).content
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
            table = wb.sheets()[0]
            title = table.row_values(0)
            nrows = table.nrows
            # rowKeys = ['merchant_name', 'simd', 'is_effect', 'effect_date', 'expiry_date', 'workerUid', 'mchid', 'agent']
            is_enter = 2
            remark = ''
            entry_coupons = 0
            ylx_entry_coupons = 0
            max_coupons = nrows-1
            is_black = history.is_black
            if is_black:
                stock_id_list = list(BlackTable.objects.all().values_list('stock_id', flat=True))
            try:
                business_no_index = title.index('商户号')
                stock_index = title.index('批次号')
                # mchid_index = title.index('微信商户号')
                nrows = table.nrows
                data = {}
                check_business_no_list_server = redis_conn.get("check_business_no_list")
                if check_business_no_list_server:
                    business_no_list = json.loads(check_business_no_list_server.decode())
                else:
                    business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
                    redis_conn.setex("check_business_no_list", 86400, json.dumps(business_no_list))
                # business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
                for i in range(1, nrows):
                    rowValues = table.row_values(i)
                    stock = str(rowValues[stock_index]).replace('.0', '').replace(' ', '')
                    business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
                    # mchid = str(rowValues[mchid_index]).replace('.0', '').replace(' ', '')
                    if stock:
                        if stock not in data:
                            data[stock] = {}
                            data[stock]['business_no'] = []
                            data[stock]['mchid'] = []
                        if business_no:
                            if not business_no.isdigit():
                                remark += f'{business_no}商户号错误,'
                                is_enter = 3
                                continue
                            if len(business_no) == 10 and (business_no.startswith('10') or business_no.startswith('60')):
                                if business_no not in business_no_list:
                                    check_merchant = Merchant.objects.filter(wyfMerchantID=business_no).first()
                                    if check_merchant:
                                        continue
                                    business_no_list.append(business_no)
                                    md5 = hashlib.md5()
                                    md5.update(str(business_no).encode('utf-8'))
                                    sign = md5.hexdigest()
                                    url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
                                    body = {
                                        'account': 'gdyouzheng',
                                        'shopId': sign
                                    }
                                    reaponse = requests.post(url=url, data=body)
                                    try:
                                        reaponse = reaponse.json()
                                    except:
                                        remark += f'{business_no}商户号添加失败,'
                                        is_enter = 3
                                        continue
                                    if 'wx_channel_merchant_id' not in reaponse:
                                        is_enter = 3
                                        remark += f'{business_no}商户号错误,'
                                        continue
                                    if reaponse['wx_channel_merchant_id'] == '':
                                        remark += f'{business_no}获取商户信息失败,'
                                        is_enter = 3
                                        continue
                                    with transaction.atomic():
                                        # 创建事务保存点
                                        save_id = transaction.savepoint()
                                        try:
                                            merchant_data = {}
                                            channel_mch_id = reaponse['channel_mch_id']
                                            channel = Channel.objects.filter(mchid=channel_mch_id).first()
                                            if channel:
                                                merchant_data['channel_id'] = channel.id
                                            merchant_data['name'] = reaponse['shopName']
                                            merchant_data['short_name'] = reaponse['shopNickname']
                                            merchant_data['wyfMerchantID'] = business_no
                                            merchant_data['ruiyinxinMerchantID'] = sign
                                            merchant_data['create_time'] = datetime.datetime.now()
                                            if reaponse['businessType'] == '1':
                                                merchant_data['subject_type'] = 3
                                            elif reaponse['businessType'] == '2':
                                                merchant_data['subject_type'] = 2
                                            elif reaponse['businessType'] == '3':
                                                merchant_data['subject_type'] = 1
                                            else:
                                                merchant_data['subject_type'] = 4
                                            level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                                            if level:
                                                merchant_data['level_id'] = level.id

                                            merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                                            merchant_data['merchantuser_id'] = merchantuser.id
                                            merchant_data['account_name'] = reaponse['cardName']
                                            merchant_data['account_bank'] = reaponse['bankName']
                                            merchant_data['account_number'] = reaponse['card']
                                            if 'latitude' in reaponse:
                                                merchant_data['latitude'] = reaponse['latitude']
                                            if 'longitude' in reaponse:
                                                merchant_data['longitude'] = reaponse['longitude']
                                            card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                                            card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                                            if card_copy_url:
                                                merchant_data['card_copy'] = card_copy
                                            merchant_data['identification_number'] = reaponse['identity']
                                            merchant_data['store_address'] = reaponse['address']
                                            store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                                            store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                            if store_header_copy_url:
                                                merchant_data['store_header_copy'] = store_header_copy
                                            merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data,wyfMerchantID=str(business_no))
                                            if not merchant.managerName:
                                                merchant.managerName = reaponse['keeper']
                                            if not merchant.managermobile:
                                                merchant.managermobile = reaponse['phone']
                                            if not merchant.prov_name:
                                                merchant.prov_name = reaponse['province']
                                                merchant.city_name = reaponse['city']
                                                merchant.district_name = reaponse['area']
                                            if not merchant.business_category:
                                                merchant.business_category_id = 1
                                            if not merchant.store_logo_copy:
                                                merchant.store_logo_copy = 'default_merchant_logo.jpg'
                                            merchant.save()
                                            if cereate and merchant.channel:
                                                store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                                store_header_copy_url = sync_img_to_local(
                                                    f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                                if store_header_copy_url:
                                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                                                store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                                store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                                                if store_indoor_copy_url:
                                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                                                merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                                merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                                                if merchantCheck_url:
                                                    MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                                            merchantchannel_data = {
                                                'merchant_id': merchant.id
                                            }
                                            if reaponse['allchannelJson']['channel_ruiyinxin']:
                                                if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                                    merchantchannel_data['channel_id'] = 2
                                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                                          merchant_id=merchant.id,
                                                                                          channel_id=merchantchannel_data['channel_id'])
                                            if reaponse['allchannelJson']['channel_sxf_tq']:
                                                if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                                    merchantchannel_data['channel_id'] = 3
                                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                                          merchant_id=merchant.id,
                                                                                          channel_id=merchantchannel_data['channel_id'])
                                            transaction.savepoint_commit(save_id)
                                        except Exception as e:
                                            print(e)
                                            remark += f'{business_no}添加商户信息失败,'
                                            continue
                                data[stock]['business_no'].append(business_no)
                            else:
                                data[stock]['mchid'].append(business_no)
                for key, value in data.items():
                    if is_black:
                        if key in stock_id_list:
                            remark += f'{key}黑名单批次,'
                            continue
                    alidiscountcoupon = AliDiscountCoupon.objects.filter(template_id=key).first()
                    if not alidiscountcoupon:
                        remark += f'批次未添加系统,'
                        continue
                    else:
                        if alidiscountcoupon.platform_id == 13:
                            remark += f'四川批次未实现此功能,'
                            continue
                        cookies = alidiscountcoupon.platform
                        headers = {'Cookie': cookies.cookie}
                    if value['business_no']:
                        query_set = list(MerchantChannel.objects.filter(merchant__wyfMerchantID__in=value['business_no']).values_list('sub_mchid', flat=True).distinct()) + value['mchid']
                    else:
                        query_set = value['mchid']
                    query_set = list(set(query_set))
                    entry_coupons += len(query_set)
                    slice_begin = 0
                    slice_end = 400
                    while True:
                        query_set1 = query_set[slice_begin: slice_end]
                        if not query_set1:
                            break
                        completemodifyinfo = ''
                        modifymchinfo = 'Add_'
                        seqcode = int(time.time()*1000)
                        for query in query_set1:
                            if query in black_sub_mchid_list:
                                remark += f'{query}黑名单商户号,'
                                continue
                            excel_data_body = []
                            completemodifyinfo += f'({query}),'
                            modifymchinfo += f'{query},'
                            excel_data_body.append(str(key))
                            excel_data_body.append(str(query))
                            excel_data_body.append(str(seqcode))
                            excel_data.append(excel_data_body)
                        check_password_url = 'https://pay.weixin.qq.com/index.php/core/risk_ctrl/verifyPasswd?g_ty=ajax'
                        check_password_body = {
                            "ecc_csrf_token": cookies.ecc_csrf_token,
                            "passwd": cookies.token,
                            "sec_factor": "1"
                        }
                        check_password_response = requests.post(url=check_password_url, data=check_password_body, headers=headers)
                        try:
                            check_password_data = json.loads(check_password_response.content)
                        except:
                            remark = 'Cookie已失效,请联系管理员!'
                            break
                        if check_password_data['errorcode'] != 0:
                            remark = '操作密码错误!请联系管理员'
                            break
                        credential = check_password_data['data']['credential']
                        submit_url = f'https://pay.weixin.qq.com/xdc/mktbatchdetails/index.php/extend/mktaccount/BudgetRevise?passwd={credential}&g_ty=ajax'
                        # remark += f'申请单:{seqcode},'
                        submit_body = {
                            "stockid": key,
                            "seqcode": seqcode,
                            "modifytype": "2",
                            "ecc_csrf_token": cookies.ecc_csrf_token,
                            "completemodifyinfo": f"{completemodifyinfo}|",
                            "modifymchinfo": f"{modifymchinfo}|Del_"
                        }
                        submit_response = requests.post(url=submit_url, data=submit_body, headers=headers)
                        try:
                            submit_data = json.loads(submit_response.content)
                        except:
                            remark = 'Cookie已失效,请联系管理员!'
                            break
                        print(submit_data)
                        if 'errcode' not in submit_data:
                            if submit_data['errorcode'] != 0:
                                remark += f'{key}批次状态有误:{submit_data["msg"]}'
                                entry_coupons -= len(value)
                                slice_begin += 400
                                slice_end += 400
                                continue
                        time.sleep(10)
                        a = 1
                        while True:
                            if a > 40:
                                break
                            query_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/query_unprocess_task?g_ty=ajax'
                            query_body = {
                                "page": "1",
                                "workflow_id": "10",
                                "business_code": seqcode,
                                "ecc_csrf_token": cookies.ecc_csrf_token,
                                "cert_id": "0",
                                "order_type": "0"
                            }
                            query_response = requests.post(url=query_url, data=query_body, headers=headers)
                            print(query_response.json())
                            if query_response.json()['data']:
                                agree_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/pass_process?g_ty=ajax'
                                agree_body = {
                                    "ecc_csrf_token": cookies.ecc_csrf_token,
                                    "process_id": query_response.json()['data']['list'][0]['process_id'],
                                }
                                agree_response = requests.post(url=agree_url, data=agree_body, headers=headers)
                                time.sleep(6)
                                break
                            time.sleep(10)
                            a += 1
                        slice_begin += 400
                        slice_end += 400
                for key, value in data.items():
                    if is_black:
                        if key in stock_id_list:
                            continue
                    try:
                        result = check_file(key, value['business_no'])
                        ylx_entry_coupons += int(result['data']['successNum'])
                    except:
                        pass
                book_name_xlsx = history.file.name.split('.')[0] + '_日志.xlsx'
                sheet_name_xlsx = 'json1'
                file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
                write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
                with open(file_path, 'rb') as recode_f:
                    file_contents = recode_f.read()
                file = pic_upload(file_contents, book_name_xlsx)
                # remark += f'申请单链接:https://circle.sutpay.cn/{file}'
                os.remove(file_path)
                history.wx_is_enter = 2
                history.ylx_is_enter = 2
                history.remark = remark
                history.max_number = max_coupons
                history.logs_file = file
                history.wx_entry_number = entry_coupons
                history.ylx_entry_number = ylx_entry_coupons
                history.save()
                if user.platform == 2:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=2).first()
                else:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=1).first()
                if re_history:
                    re_history.wx_is_enter = 1
                    re_history.ylx_is_enter = 1
                    re_history.save()
                    update_stock_usable_merchant.delay({'id': re_history.id})
                return '添加成功'
            except Exception as e:
                print(e)
                history.wx_is_enter = 3
                history.ylx_is_enter = 3
                history.save()
                if user.platform == 2:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=2).first()
                else:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=1).first()
                if re_history:
                    re_history.wx_is_enter = 1
                    re_history.ylx_is_enter = 1
                    re_history.save()
                    update_stock_usable_merchant.delay({'id': re_history.id})
                return '失败'
        else:
            # rowKeys = ['merchant_name', 'simd', 'is_effect', 'effect_date', 'expiry_date', 'workerUid', 'mchid', 'agent']
            is_enter = 2
            merchant_array = json.loads(history.test)
            remark = ''
            entry_coupons = 0
            ylx_entry_coupons = 0
            max_coupons = len(merchant_array)
            try:
                data = {}
                cookies = Channel.objects.get(id=4)
                headers = {'Cookie': cookies.cookie}
                redis_conn = get_redis_connection("cache")
                check_business_no_list_server = redis_conn.get("check_business_no_list")
                if check_business_no_list_server:
                    business_no_list = json.loads(check_business_no_list_server.decode())
                else:
                    business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
                    redis_conn.setex("check_business_no_list", 86400, json.dumps(business_no_list))
                # business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
                stock = history.alidiscountcoupon.template_id
                if stock not in data:
                    data[stock] = {}
                    data[stock]['business_no'] = []
                    data[stock]['mchid'] = []
                for business_no in merchant_array:
                    business_no = str(business_no).replace('.0', '').replace(' ', '')
                    if not business_no.isdigit():
                        remark += f'{business_no}商户号错误,'
                        is_enter = 3
                        continue
                    if len(business_no) == 10 and (business_no.startswith('10') or business_no.startswith('60')):
                        if business_no not in business_no_list:
                            check_merchant = Merchant.objects.filter(wyfMerchantID=business_no).first()
                            if check_merchant:
                                continue
                            business_no_list.append(business_no)
                            md5 = hashlib.md5()
                            md5.update(str(business_no).encode('utf-8'))
                            sign = md5.hexdigest()
                            url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
                            body = {
                                'account': 'gdyouzheng',
                                'shopId': sign
                            }
                            reaponse = requests.post(url=url, data=body)
                            try:
                                reaponse = reaponse.json()
                            except:
                                remark += f'{business_no}商户号添加失败,'
                                is_enter = 3
                                continue
                            if 'wx_channel_merchant_id' not in reaponse:
                                is_enter = 3
                                remark += f'{business_no}商户号错误,'
                                continue
                            if reaponse['wx_channel_merchant_id'] == '':
                                remark += f'{business_no}获取商户信息失败,'
                                is_enter = 3
                                continue
                            with transaction.atomic():
                                # 创建事务保存点
                                save_id = transaction.savepoint()
                                try:
                                    merchant_data = {}
                                    channel_mch_id = reaponse['channel_mch_id']
                                    channel = Channel.objects.filter(mchid=channel_mch_id).first()
                                    if channel:
                                        merchant_data['channel_id'] = channel.id
                                    merchant_data['name'] = reaponse['shopName']
                                    merchant_data['short_name'] = reaponse['shopNickname']
                                    merchant_data['wyfMerchantID'] = business_no
                                    merchant_data['ruiyinxinMerchantID'] = sign
                                    merchant_data['create_time'] = datetime.datetime.now()
                                    if reaponse['businessType'] == '1':
                                        merchant_data['subject_type'] = 3
                                    elif reaponse['businessType'] == '2':
                                        merchant_data['subject_type'] = 2
                                    elif reaponse['businessType'] == '3':
                                        merchant_data['subject_type'] = 1
                                    else:
                                        merchant_data['subject_type'] = 4
                                    level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                                    if level:
                                        merchant_data['level_id'] = level.id

                                    merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                                    merchant_data['merchantuser_id'] = merchantuser.id
                                    merchant_data['account_name'] = reaponse['cardName']
                                    merchant_data['account_bank'] = reaponse['bankName']
                                    merchant_data['account_number'] = reaponse['card']
                                    card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                                    card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                                    if card_copy_url:
                                        merchant_data['card_copy'] = card_copy
                                    merchant_data['identification_number'] = reaponse['identity']
                                    merchant_data['store_address'] = reaponse['address']
                                    store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                                    store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                    if store_header_copy_url:
                                        merchant_data['store_header_copy'] = store_header_copy
                                    merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data,wyfMerchantID=str(business_no))
                                    if not merchant.managerName:
                                        merchant.managerName = reaponse['keeper']
                                    if not merchant.managermobile:
                                        merchant.managermobile = reaponse['phone']
                                    if not merchant.prov_name:
                                        merchant.prov_name = reaponse['province']
                                        merchant.city_name = reaponse['city']
                                        merchant.district_name = reaponse['area']
                                    if not merchant.business_category:
                                        merchant.business_category_id = 1
                                    if not merchant.store_logo_copy:
                                        merchant.store_logo_copy = 'default_merchant_logo.jpg'
                                    merchant.save()
                                    if cereate and merchant.channel:
                                        store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                        store_header_copy_url = sync_img_to_local(
                                            f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                        if store_header_copy_url:
                                            MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                                        store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                        store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                                        if store_indoor_copy_url:
                                            MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                                        merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                        merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                                        if merchantCheck_url:
                                            MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                                    merchantchannel_data = {
                                        'merchant_id': merchant.id
                                    }
                                    if reaponse['allchannelJson']['channel_ruiyinxin']:
                                        if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                            merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                            merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                            merchantchannel_data['channel_id'] = 2
                                            merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                                  merchant_id=merchant.id,
                                                                                  channel_id=merchantchannel_data['channel_id'])
                                    if reaponse['allchannelJson']['channel_sxf_tq']:
                                        if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                            merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                            merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                            merchantchannel_data['channel_id'] = 3
                                            merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                                  merchant_id=merchant.id,
                                                                                  channel_id=merchantchannel_data['channel_id'])
                                    transaction.savepoint_commit(save_id)
                                except Exception as e:
                                    print(e)
                                    remark += f'{business_no}添加商户信息失败,'
                                    continue
                        data[stock]['business_no'].append(business_no)
                    else:
                        data[stock]['mchid'].append(business_no)
                for key, value in data.items():
                    if value['business_no']:
                        query_set = list(MerchantChannel.objects.filter(merchant__wyfMerchantID__in=value['business_no']).values_list('sub_mchid', flat=True).distinct()) + value['mchid']
                    else:
                        query_set = value['mchid']
                    query_set = list(set(query_set))
                    entry_coupons += len(query_set)
                    slice_begin = 0
                    slice_end = 400
                    while True:
                        cookies = Channel.objects.get(id=4)
                        headers = {'Cookie': cookies.cookie}
                        query_set1 = query_set[slice_begin: slice_end]
                        if not query_set1:
                            break
                        completemodifyinfo = ''
                        modifymchinfo = 'Add_'
                        seqcode = int(time.time()*1000)
                        for query in query_set1:
                            if query in black_sub_mchid_list:
                                remark += f'{query}黑名单商户号,'
                                continue
                            excel_data_body = []
                            alidiscountcouponmerchant_data = {}
                            alidiscountcouponmerchant_data['alidiscountcoupon_id'] = history.alidiscountcoupon_id
                            alidiscountcouponmerchant_data['sub_mchid'] = query
                            merchant = MerchantChannel.objects.filter(sub_mchid=query).first()
                            if merchant:
                                alidiscountcouponmerchant_data['merchant_id'] = merchant.merchant_id
                                alidiscountcouponmerchant_data['channel_id'] = merchant.channel_id
                            AliDiscountCouponMerchant.objects.get_or_create(
                                defaults=alidiscountcouponmerchant_data,
                                sub_mchid=query,
                                alidiscountcoupon_id=history.alidiscountcoupon_id
                            )
                            completemodifyinfo += f'({query}),'
                            modifymchinfo += f'{query},'
                            excel_data_body.append(str(key))
                            excel_data_body.append(str(query))
                            excel_data_body.append(str(seqcode))
                            excel_data.append(excel_data_body)
                        check_password_url = 'https://pay.weixin.qq.com/index.php/core/risk_ctrl/verifyPasswd?g_ty=ajax'
                        check_password_body = {
                            "ecc_csrf_token": cookies.ecc_csrf_token,
                            "passwd": "bCtRZmxSa3drQUhGK2VnNEwySTZJMmRRRWpFa1dUMCsrc24xVUNZdGdmUGpqNWlXY25WdG9NZEdMWmxUdURERi8yK1ZDcWlTaXhQYkpmV2ZmclNJWXk4SkxLVWZ2SUxCTUNiVmxhWm5MZmNpcXFZNW4xYXV1NDVqYVBEVG5Cbkc5aWFtZlF4Nk5ZSXlsNFdEOTJrOW5RQTFiSGlhWG1lY3JNTDVDRy8xRVhnPQ==",
                            "sec_factor": "1"
                        }
                        check_password_response = requests.post(url=check_password_url, data=check_password_body, headers=headers)
                        try:
                            check_password_data = json.loads(check_password_response.content)
                        except:
                            remark = 'Cookie已失效,请联系管理员!'
                            break
                        if check_password_data['errorcode'] != 0:
                            remark = '操作密码错误!请联系管理员'
                            break
                        credential = check_password_data['data']['credential']
                        submit_url = f'https://pay.weixin.qq.com/xdc/mktbatchdetails/index.php/extend/mktaccount/BudgetRevise?passwd={credential}&g_ty=ajax'
                        # remark += f'申请单:{seqcode},'
                        submit_body = {
                            "stockid": key,
                            "seqcode": seqcode,
                            "modifytype": "2",
                            "ecc_csrf_token": cookies.ecc_csrf_token,
                            "completemodifyinfo": f"{completemodifyinfo}|",
                            "modifymchinfo": f"{modifymchinfo}|Del_"
                        }
                        submit_response = requests.post(url=submit_url, data=submit_body, headers=headers)
                        try:
                            submit_data = json.loads(submit_response.content)
                        except:
                            remark = 'Cookie已失效,请联系管理员!'
                            break
                        print(submit_data)
                        if 'errcode' not in submit_data:
                            if submit_data['errorcode'] != 0:
                                remark += f'{key}批次状态有误:{submit_data["msg"]}'
                                entry_coupons -= len(value)
                                slice_begin += 400
                                slice_end += 400
                                break
                        time.sleep(10)
                        a = 1
                        while True:
                            if a > 40:
                                break
                            query_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/query_unprocess_task?g_ty=ajax'
                            query_body = {
                                "page": "1",
                                "workflow_id": "10",
                                "business_code": seqcode,
                                "ecc_csrf_token": cookies.ecc_csrf_token,
                                "cert_id": "0",
                                "order_type": "0"
                            }
                            query_response = requests.post(url=query_url, data=query_body, headers=headers)
                            print(query_response.json())
                            if query_response.json()['data']:
                                agree_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/pass_process?g_ty=ajax'
                                agree_body = {
                                    "ecc_csrf_token": cookies.ecc_csrf_token,
                                    "process_id": query_response.json()['data']['list'][0]['process_id'],
                                }
                                agree_response = requests.post(url=agree_url, data=agree_body, headers=headers)
                                time.sleep(6)
                                break
                            time.sleep(10)
                            a += 1
                        slice_begin += 400
                        slice_end += 400
                for key, value in data.items():
                    try:
                        result = check_file(key, value['business_no'])
                        ylx_entry_coupons += int(result['data']['successNum'])
                    except:
                        pass
                book_name_xlsx = history.file.name.split('.')[0] + '_日志.xlsx'
                sheet_name_xlsx = 'json1'
                file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
                write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
                with open(file_path, 'rb') as recode_f:
                    file_contents = recode_f.read()
                file = pic_upload(file_contents, book_name_xlsx)
                # remark += f'申请单链接:https://circle.sutpay.cn/{file}'
                os.remove(file_path)
                history.wx_is_enter = 2
                history.ylx_is_enter = 2
                history.remark = remark
                history.max_number = max_coupons
                history.logs_file = file
                history.wx_entry_number = entry_coupons
                history.ylx_entry_number = ylx_entry_coupons
                history.save()
                if user.platform == 2:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=2).first()
                else:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=1).first()
                if re_history:
                    re_history.wx_is_enter = 1
                    re_history.ylx_is_enter = 1
                    re_history.save()
                    update_stock_usable_merchant.delay({'id': re_history.id})
                return '添加成功'
            except Exception as e:
                print(e)
                history.wx_is_enter = 3
                history.ylx_is_enter = 3
                history.save()
                if user.platform == 2:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=2).first()
                else:
                    re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=1).first()
                if re_history:
                    re_history.wx_is_enter = 1
                    re_history.ylx_is_enter = 1
                    re_history.save()
                    update_stock_usable_merchant.delay({'id': re_history.id})
                return '失败'
    except Exception as e:
        print(e)
        history.wx_is_enter = 3
        history.ylx_is_enter = 3
        history.save()
        if user.platform == 2:
            re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=2).first()
        else:
            re_history = UpdateStockUsableMerchant.objects.filter(wx_is_enter=4, operation_user__platform=1).first()
        if re_history:
            re_history.wx_is_enter = 1
            re_history.ylx_is_enter = 1
            re_history.save()
            update_stock_usable_merchant.delay({'id': re_history.id})
        return '失败'


@shared_task
def execute_express_quest(data):
    id = data['id']
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        execute_express_quest.delay({'id': id})
        return '移交其他服务器处理'
    history = ExpressQuest.objects.get(id=id)
    excel_data = []
    excel_data_title = ['订单号', '快递单号', '快递类型', '状态', '失败原因']
    excel_data.append(excel_data_title)
    file_contents = requests.get(history.file.url).content
    wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
    table = wb.sheets()[0]
    title = table.row_values(0)
    nrows = table.nrows
    success_number = 0
    line_number = nrows-1
    error_number = 0
    try:
        order_id_index = title.index('订单号')
        express_number_index = title.index('快递单号')
        express_type_id_index = title.index('快递类型')
        nrows = table.nrows
        for i in range(1, nrows):
            excel_data_body = []
            rowValues = table.row_values(i)
            order_id = str(rowValues[order_id_index]).replace('.0', '').replace(' ', '')
            express_number_str = str(rowValues[express_number_index]).replace('.0', '').replace(' ', '').replace('，', ',')
            express_type_id_str = str(rowValues[express_type_id_index]).replace('.0', '').replace(' ', '').replace('，', ',')
            order = OrderInfo.objects.filter(id=order_id).first()
            excel_data_body.append(order_id)
            excel_data_body.append(str(express_number_str))
            excel_data_body.append(str(express_type_id_str))
            express_number_list = express_number_str.split(',')
            express_type_id_list = express_type_id_str.split(',')
            if not history.user:
                if order.merchant.merchantuser_id != history.merchantuser_id:
                    excel_data_body.append('失败')
                    excel_data_body.append('订单号不属于当前商家')
                    error_number += 1
                    excel_data.append(excel_data_body)
                    continue
            if not order:
                excel_data_body.append('失败')
                excel_data_body.append('订单号不存在')
                error_number += 1
                excel_data.append(excel_data_body)
                continue
            if order.state != 9:
                excel_data_body.append('失败')
                excel_data_body.append('订单状态有误')
                error_number += 1
                excel_data.append(excel_data_body)
                continue
            if len(express_number_list) != len(express_type_id_list):
                excel_data_body.append('失败')
                excel_data_body.append('快递单号跟快递类型数目对不上')
                error_number += 1
                excel_data.append(excel_data_body)
                continue
            check_status = 1
            for index in range(len(express_type_id_list)):
                express_type_id = express_type_id_list[index].replace(' ', '')
                express_number = express_number_list[index].replace(' ', '')
                try:
                    ExpressType.objects.get(id=express_type_id)
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append('无效快递类型')
                    error_number += 1
                    check_status = 0
                    break
                express_data = {}
                express_data['express_type_id'] = express_type_id
                express_data['express_number'] = express_number
                express, create = Express.objects.get_or_create(defaults=express_data,express_number=express_number)
                relevance_data = {}
                relevance_data['order_id'] = order_id
                relevance_data['express_id'] = express.id
                ExpressOrderRelevance.objects.get_or_create(defaults=relevance_data,order_id=relevance_data['order_id'], express_id=relevance_data['express_id'])
            if check_status == 1:
                excel_data_body.append('成功')
                excel_data_body.append('')
                success_number += 1
                order.state = 6
                order.save()
                OrderAdvance.objects.create(
                    order_id=order_id,
                    status=3,
                    remark='添加快递单号',
                    user_id=history.user_id,
                    merchantuser_id=history.merchantuser_id
                )
                try:
                    express_send.delay({'id': order.id, 'express_id': express.id})
                except:
                    pass
            excel_data.append(excel_data_body)
        book_name_xlsx = history.file.name.split('.')[0] + '_日志.xlsx'
        sheet_name_xlsx = 'json1'
        file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
        write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
        with open(file_path, 'rb') as recode_f:
            file_contents = recode_f.read()
        file = pic_upload(file_contents, book_name_xlsx)
        os.remove(file_path)
        history.is_enter = 2
        history.line_number = line_number
        history.success_number = success_number
        history.logs_file = file
        history.error_number = error_number
        history.save()
        re_history = ExpressQuest.objects.filter(is_enter=4).first()
        if re_history:
            re_history.is_enter = 1
            re_history.save()
            execute_express_quest.delay({'id': re_history.id})
        return '添加成功'
    except Exception as e:
        logger.error(str(e))
        print(e)
        history.is_enter = 3
        history.save()
        re_history = UpdateStockUsableMerchant.objects.filter(is_enter=4).first()
        if re_history:
            re_history.is_enter = 1
            re_history.save()
            execute_express_quest.delay({'id': re_history.id})
        return '失败'


def rsa_long_encrypt(message, pub_key):
    rsakey = RSA.importKey(pub_key)
    cipher = sign_PKCS1_v1_5.new(rsakey)
    rand_hash = SHA256.new()
    rand_hash.update(message.encode())
    signature = cipher.sign(rand_hash)
    return str((base64.b64encode(signature)), encoding="utf-8")


@shared_task
def sync_activity_merchant(data):
    id = data['id']
    query = AliDiscountCoupon.objects.get(id=id)
    query.last_update_time = datetime.datetime.now()
    query.save()
    pub_key = query.platform.pub_key
    mchid = query.platform.mchid
    serial_no = query.platform.serial_no
    offset = 0
    limit = 50
    sub_mchid_list = []
    while True:
        UFDODER_URL = f'https://api.mch.weixin.qq.com/v3/marketing/favor/stocks/{query.template_id}/merchants?offset={offset}&limit={limit}&stock_creator_mchid={mchid}'
        nonce_str = random_str()
        timestamp = int(time.time())
        message = f'GET\n/v3/marketing/favor/stocks/{query.template_id}/merchants?offset={offset}&limit={limit}&stock_creator_mchid={mchid}\n{timestamp}\n{nonce_str}\n\n'
        signature = rsa_long_encrypt(message, pub_key)
        headers = {
            'Authorization': f'WECHATPAY2-SHA256-RSA2048 mchid="{mchid}",nonce_str="{nonce_str}",signature="{signature}",timestamp="{timestamp}",serial_no="{serial_no}"'}
        response = requests.request('get', UFDODER_URL, headers=headers)
        dict_data = json.loads(response.content)
        print(dict_data)
        print(query.template_id)
        if 'data' not in dict_data:
            break
        offset += 1
        # if offset >= 60:
        #     break
        sub_mchid_list += dict_data['data']
    if sub_mchid_list:
        check_sub_mchid_list = list(AliDiscountCouponMerchant.objects.filter(sub_mchid__in=sub_mchid_list, alidiscountcoupon_id=id).values_list('sub_mchid', flat=True))
        new_sub_mchid_list = [i for i in sub_mchid_list if i not in check_sub_mchid_list]
        if new_sub_mchid_list:
            merchant_list = MerchantChannel.objects.filter(sub_mchid__in=sub_mchid_list).values('channel_id', 'merchant_id', 'sub_mchid')
            merchant_dict = {}
            for merchant in merchant_list:
                merchant_dict[merchant['sub_mchid']] = merchant
            conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                   port=settings.DATABASES['default']['PORT'],
                                   database=settings.DATABASES['default']['NAME'],
                                   user=settings.DATABASES['default']['USER'],
                                   password=settings.DATABASES['default']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            try:
                sql = f'''INSERT INTO tb_ali_discount_coupon_merchant(alidiscountcoupon_id,sub_mchid,channel_id,merchant_id) VALUES '''
                for sub_mchid in new_sub_mchid_list:
                    insert_data = []
                    insert_data.append(query.id)
                    insert_data.append(sub_mchid)
                    if sub_mchid in merchant_dict:
                        insert_data.append(merchant_dict[sub_mchid]['channel_id'])
                        insert_data.append(merchant_dict[sub_mchid]['merchant_id'])
                    else:
                        insert_data.append('null')
                        insert_data.append('null')
                    sql += str(tuple(insert_data)).replace("'null'", 'null')
                    sql += ','
                sql = sql[:-1]
                sql += ';'
                print(sql)
                cursor.execute(sql)
                conn.commit()
            except Exception as e:
                print(e)
                conn.rollback()
            # try:
            #     sql = f'''UPDATE tb_ali_discount_coupon_merchant,tb_merchant_channel SET tb_ali_discount_coupon_merchant.channel_id=tb_merchant_channel.channel_id,tb_ali_discount_coupon_merchant.merchant_id=tb_merchant_channel.merchant_id WHERE tb_ali_discount_coupon_merchant.alidiscountcoupon_id={query.id} AND tb_ali_discount_coupon_merchant.merchant_id IS NULL AND tb_ali_discount_coupon_merchant.sub_mchid=tb_merchant_channel.sub_mchid;'''
            #     cursor.execute(sql)
            #     conn.commit()
            # except Exception as e:
            #     print(e)
            #     conn.rollback()
            cursor.close()
            conn.close()
    #     for sub_mchid in sub_mchid_list:
    #         alidiscountcouponmerchant_data = {}
    #         alidiscountcouponmerchant_data['alidiscountcoupon_id'] = query.id
    #         alidiscountcouponmerchant_data['sub_mchid'] = sub_mchid
    #         merchant = MerchantChannel.objects.filter(sub_mchid=sub_mchid).first()
    #         if merchant:
    #             alidiscountcouponmerchant_data['merchant_id'] = merchant.merchant_id
    #             alidiscountcouponmerchant_data['channel_id'] = merchant.channel_id
    #         AliDiscountCouponMerchant.objects.get_or_create(
    #             defaults=alidiscountcouponmerchant_data,
    #             sub_mchid=sub_mchid,
    #             alidiscountcoupon_id=query.id
    #         )
    # query_set = AliDiscountCouponMerchant.objects.filter(merchant_id__isnull=True, alidiscountcoupon_id=id)
    # for query in query_set:
    #     merchant = MerchantChannel.objects.filter(sub_mchid=query.sub_mchid).first()
    #     # print(merchant.id)
    #     if merchant:
    #         query.channel_id = merchant.channel_id
    #         query.merchant_id = merchant.merchant_id
    #         query.save()
    query.syn_status = 1
    query.save()
    return '同步成功'


def periods_choices(number):
    number = Decimal(number)
    if number <= Decimal('50'):
        return 1
    elif number > Decimal('50') and number <= Decimal('100'):
        return 2
    elif number > Decimal('100') and number <= Decimal('150'):
        return 3
    elif number > Decimal('150') and number <= Decimal('250'):
        return 4
    else:
        return 5


@shared_task
def pull_waive_payment_merchant(data):
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        pull_waive_payment_merchant.delay(data)
        return '移交其他服务器处理'
    id = data['id']
    waivepaymentadvance = WaivePaymentAdvance.objects.get(id=id)
    waivepaymentactivity = waivepaymentadvance.waivepaymentactivity
    file_contents = requests.get(waivepaymentadvance.file.url).content
    wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
    table = wb.sheets()[0]
    title = table.row_values(0)
    return_msg = ''
    order_url = 'https://manage.gdwxyf.com/internal/searchorders.do'
    order_body = {
        "account": "gdyouzheng",
        "start": waivepaymentadvance.trade_time_begin.strftime('%Y-%m-%d %H:%M:%S'),
        "end": waivepaymentadvance.trade_time_end.strftime('%Y-%m-%d %H:%M:%S'),
        "orderStatus": "1"
    }
    try:
        business_no_index = title.index('商户号')
        is_draw_index = title.index('是否中奖')
        periods_index = title.index('期数')
        nrows = table.nrows
        rowValues = table.row_values(1)
        periods = str(rowValues[periods_index]).replace('.0', '').replace(' ', '')
        if periods == '一':
            number_choices = {
                1: 197,
                2: 59,
                3: 12,
                4: 4,
                5: 2
            }
        elif periods == '二':
            number_choices = {
                1: 196,
                2: 59,
                3: 12,
                4: 4,
                5: 3
            }
        elif periods == '三':
            number_choices = {
                1: 196,
                2: 59,
                3: 12,
                4: 3,
                5: 2
            }
        elif periods == '四':
            number_choices = {
                1: 196,
                2: 59,
                3: 12,
                4: 3,
                5: 2
            }
        elif periods == '五':
            number_choices = {
                1: 196,
                2: 58,
                3: 12,
                4: 3,
                5: 2
            }
        else:
            number_choices = {
                1: 110,
                2: 50,
                3: 50,
                4: 8,
                5: 0
            }
        for i in range(1, nrows):
            rowValues = table.row_values(i)
            business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
            is_draw = str(rowValues[is_draw_index]).replace('.0', '').replace(' ', '')
            waivepaymentmerchant_data = {}
            waivepaymentmerchant_data['waivepaymentactivity_id'] = waivepaymentactivity.id
            waivepaymentmerchant_data['waivepaymentadvance_id'] = waivepaymentadvance.id
            waivepaymentmerchant_data['business_no'] = business_no
            waivepaymentmerchant_data['nonce_str'] = random_str()
            partner_trade_no = f'100006{str(datetime.datetime.now()).replace("-", "").replace(":", "").replace(" ", "").replace(".", "")}{"%06d" % randint(0, 999999)}'
            waivepaymentmerchant_data['partner_trade_no'] = partner_trade_no
            waivepaymentmerchant_data['periods'] = periods
            if waivepaymentactivity.is_repeat:
                waivepaymentmerchant = WaivePaymentMerchant.objects.filter(waivepaymentactivity_id=waivepaymentactivity.id, business_no=business_no).first()
                if waivepaymentmerchant and waivepaymentmerchant.payment_state == 6:
                    waivepaymentmerchant = WaivePaymentMerchant.objects.create(**waivepaymentmerchant_data)
                    create = True
                else:
                    waivepaymentmerchant, create = WaivePaymentMerchant.objects.get_or_create(
                        waivepaymentactivity_id=waivepaymentactivity.id,
                        business_no=business_no,
                        defaults=waivepaymentmerchant_data
                    )
            else:
                waivepaymentmerchant = WaivePaymentMerchant.objects.create(**waivepaymentmerchant_data)
                create = True
            if len(business_no) == 10 and (business_no.startswith('10') or business_no.startswith('60')):
                if create:
                    order_body["shopId"] = business_no
                    order_response = requests.post(url=order_url, data=order_body)
                    order_result = order_response.json()['showList']
                    if order_result:
                        waivepaymentorder_list = []
                        for order in order_result:
                            waivepaymentorder_data = {}
                            waivepaymentorder_data['waivepaymentactivity_id'] = waivepaymentactivity.id
                            waivepaymentorder_data['waivepaymentmerchant_id'] = waivepaymentmerchant.id
                            waivepaymentorder_data['order_no'] = order['orderId']
                            if order['orderType'] == '微信':
                                waivepaymentorder_data['trade_type'] = 2
                                waivepaymentorder_data['trade_no'] = order['channelTradeNo']
                            elif order['orderType'] == '支付宝':
                                waivepaymentorder_data['trade_type'] = 1
                                waivepaymentorder_data['trade_no'] = order['channelTradeNo']
                            elif order['orderType'] == '邮储手机银行':
                                waivepaymentorder_data['trade_type'] = 4
                                waivepaymentorder_data['trade_no'] = order['channelTradeNo']
                            else:
                                waivepaymentorder_data['trade_type'] = 3
                            waivepaymentorder_data['rate_money'] = order['commission']
                            waivepaymentorder_data['trade_money'] = order['totalFee']
                            waivepaymentorder_data['settlement_money'] = order['paidout']
                            waivepaymentorder_data['trade_time_begin'] = order['startTime']
                            waivepaymentorder_data['trade_time_end'] = order['timeEnd']
                            waivepaymentorder_data['channel'] = order['channel']
                            waivepaymentorder_data['channel'] = order['channel']
                            waivepaymentorder, create = WaivePaymentOrder.objects.get_or_create(
                                defaults=waivepaymentorder_data,
                                trade_money=waivepaymentorder_data['order_no'],
                                waivepaymentmerchant_id=waivepaymentorder_data['waivepaymentmerchant_id']
                            )
                            if create:
                                number = number_choices[periods_choices(waivepaymentorder.trade_money)]
                                if number > 0:
                                    waivepaymentorder_list.append(waivepaymentorder.id)
                        if waivepaymentorder_list:
                            shuffle(waivepaymentorder_list)
                            waivepaymentorder = WaivePaymentOrder.objects.get(id=waivepaymentorder_list[0])
                            waivepaymentorder.is_hit = 1
                            waivepaymentorder.save()
                            if is_draw == '1':
                                if waivepaymentorder.trade_money < Decimal('8.8'):
                                    waivepaymentmerchant.amount = Decimal('8.8')
                                elif waivepaymentorder.trade_money > Decimal('388'):
                                    waivepaymentmerchant.amount = Decimal('388')
                                else:
                                    waivepaymentmerchant.amount = waivepaymentorder.trade_money
                                number_choices[periods_choices(waivepaymentorder.trade_money)] -= 1
                            else:
                                waivepaymentmerchant.amount = Decimal('0')
                                waivepaymentmerchant.payment_type = 0
                        else:
                            waivepaymentmerchant.payment_state = 6
                            waivepaymentmerchant.return_msg = '无满足条件订单'
                            return_msg += f'{business_no}无满足条件订单,'
                    else:
                        waivepaymentmerchant.payment_state = 6
                        waivepaymentmerchant.return_msg = '无交易记录'
                        return_msg += f'{business_no}无交易记录,'
                else:
                    waivepaymentmerchant.return_msg = '重复参与'
                    return_msg += f'{business_no}重复添加,'
                merchant = Merchant.objects.filter(wyfMerchantID=str(business_no)).first()
                if not merchant:
                    md5 = hashlib.md5()
                    md5.update(str(business_no).encode('utf-8'))
                    sign = md5.hexdigest()
                    url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
                    body = {
                        'account': 'gdyouzheng',
                        'shopId': sign
                    }
                    reaponse = requests.post(url=url, data=body)
                    try:
                        reaponse = reaponse.json()
                    except:
                        return_msg += f'{business_no}商户号添加失败,'
                        continue
                    if 'wx_channel_merchant_id' not in reaponse:
                        return_msg += f'{business_no}商户号错误,'
                        continue
                    if reaponse['wx_channel_merchant_id'] == '':
                        return_msg += f'{business_no}获取商户信息失败,'
                        continue
                    with transaction.atomic():
                        # 创建事务保存点
                        save_id = transaction.savepoint()
                        try:
                            merchant_data = {}
                            channel_mch_id = reaponse['channel_mch_id']
                            channel = Channel.objects.filter(mchid=channel_mch_id).first()
                            if channel:
                                merchant_data['channel_id'] = channel.id
                            merchant_data['name'] = reaponse['shopName']
                            merchant_data['short_name'] = reaponse['shopNickname']
                            merchant_data['wyfMerchantID'] = business_no
                            merchant_data['ruiyinxinMerchantID'] = sign
                            merchant_data['create_time'] = datetime.datetime.now()
                            if reaponse['businessType'] == '1':
                                merchant_data['subject_type'] = 3
                            elif reaponse['businessType'] == '2':
                                merchant_data['subject_type'] = 2
                            elif reaponse['businessType'] == '3':
                                merchant_data['subject_type'] = 1
                            else:
                                merchant_data['subject_type'] = 4
                            level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                            if level:
                                merchant_data['level_id'] = level.id
                            merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                            merchant_data['merchantuser_id'] = merchantuser.id
                            merchant_data['account_name'] = reaponse['cardName']
                            merchant_data['account_bank'] = reaponse['bankName']
                            merchant_data['account_number'] = reaponse['card']
                            if 'latitude' in reaponse:
                                merchant_data['latitude'] = reaponse['latitude']
                            if 'longitude' in reaponse:
                                merchant_data['longitude'] = reaponse['longitude']
                            card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                            card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                            if card_copy_url:
                                merchant_data['card_copy'] = card_copy
                            merchant_data['identification_number'] = reaponse['identity']
                            merchant_data['store_address'] = reaponse['address']
                            store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                            store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                            if store_header_copy_url:
                                merchant_data['store_header_copy'] = store_header_copy
                            merchant, create = Merchant.objects.update_or_create(defaults=merchant_data,wyfMerchantID=str(business_no))
                            if not merchant.managerName:
                                merchant.managerName = reaponse['keeper']
                            if not merchant.managermobile:
                                merchant.managermobile = reaponse['phone']
                            if not merchant.prov_name:
                                merchant.prov_name = reaponse['province']
                                merchant.city_name = reaponse['city']
                                merchant.district_name = reaponse['area']
                            if not merchant.business_category:
                                merchant.business_category_id = 1
                            if not merchant.store_logo_copy:
                                merchant.store_logo_copy = 'default_merchant_logo.jpg'
                            merchant.save()
                            if create and merchant.channel:
                                store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                store_header_copy_url = sync_img_to_local(
                                    f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                if store_header_copy_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                                store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                                if store_indoor_copy_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                                merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                                if merchantCheck_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                            merchantchannel_data = {
                                'merchant_id': merchant.id
                            }
                            if reaponse['allchannelJson']['channel_ruiyinxin']:
                                if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                    merchantchannel_data['channel_id'] = 2
                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                          merchant_id=merchant.id,
                                                                          channel_id=merchantchannel_data['channel_id'])
                            if reaponse['allchannelJson']['channel_sxf_tq']:
                                if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                    merchantchannel_data['channel_id'] = 3
                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                          merchant_id=merchant.id,
                                                                          channel_id=merchantchannel_data['channel_id'])
                            waivepaymentmerchant.merchant_id = merchant.id
                            waivepaymentmerchant.merchant_user_id = merchant.merchantuser_id
                            waivepaymentmerchant.level_id = merchant.level_id
                            transaction.savepoint_commit(save_id)
                        except Exception as e:
                            print(e)
                            return_msg += f'{business_no}添加商户信息失败,'
                            continue
                else:
                    waivepaymentmerchant.merchant_id = merchant.id
                    waivepaymentmerchant.merchant_user_id = merchant.merchantuser_id
                    waivepaymentmerchant.level_id = merchant.level_id
            else:
                waivepaymentmerchant.return_msg = '无效商户号'
                waivepaymentmerchant.payment_state = 6
                return_msg += f'{business_no}无效商户号,'
            waivepaymentmerchant.save()
        waivepaymentadvance.return_msg = return_msg
        waivepaymentadvance.save()
        return '添加成功'
    except Exception as e:
        print(e)
        return '失败'


@shared_task
def synchronization_garbage_data(data):
    id = data['id']
    start = data['start']
    end = data['end']
    history = UpdateStockUsableMerchant.objects.get(id=id)
    file_contents = requests.get(history.file.url).content
    wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
    table = wb.sheets()[0]
    title = table.row_values(0)
    nrows = table.nrows
    business_no_index = title.index('商户号')
    remark = ''
    for i in range(1, nrows):
        rowValues = table.row_values(i)
        business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
        if len(business_no) == 10 and (business_no.startswith('10') or business_no.startswith('60')):
            try:
                synchronization_kl_merchant(start, end, business_no)
            except Exception as e:
                remark += f'{business_no}录入错误,'
        else:
            remark += f'{business_no}商户号格式错误,'
    history.remark = remark
    history.save()
    return '导入成功'


@shared_task
def synchronization_garbage_order(data):
    start_time = data['start']
    end_time = data['end']
    business_no = data['business_no']
    a = 0
    b = ((datetime.datetime.strptime(start_time, "%Y-%m-%d") - datetime.datetime.strptime(end_time, "%Y-%m-%d")).days + 1) * 2
    start_time = start_time + ' 00:00:00'
    while True:
        if a >= b:
            break
        end_time = (datetime.datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S") + datetime.timedelta(hours=12)).strftime("%Y-%m-%d %H:%M:%S")
        if business_no:
            synchronization_kl_merchant(start_time, end_time, business_no)
        else:
            synchronization_kl_order(start_time, end_time)
        start_time = end_time
        a += 1
    return '导入成功'


@shared_task
def synchronization_settlementrecord(data):
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        synchronization_settlementrecord.delay(data)
        return '移交其他服务器处理'
    start_time = data['start']
    end_time = data['end']
    merchant_id = data['merchant_id']
    user_id = data['user_id']
    merchant = Merchant.objects.get(id=merchant_id)
    channel_mchid = MerchantChannel.objects.filter(merchant_id=merchant_id, channel_id=2)
    if channel_mchid:
        channel_mchid = channel_mchid.first().channel_mchid
        s_tranDates =datetime.datetime.strptime(start_time, "%Y-%m-%d").strftime("%Y%m%d")
        s_tranDatee = datetime.datetime.strptime(end_time, "%Y-%m-%d").strftime("%Y%m%d")
        result = query_transettle(channel_mchid, s_tranDates, s_tranDatee)['result']
        if result:
            result = result['list']
            for i in result:
                if i['settleType'] in ['16', '10', '02']:
                    if i['status'] == '出款中':
                        continue
                    settlement_time = datetime.datetime.strptime(str(i['settDate']), "%Y%m%d%H%M%S")
                    settlerecord_data = {}
                    settlerecord_data['merchant_id'] = merchant_id
                    settlerecord_data['account_name'] = i['cardName']
                    settlerecord_data['account_bank'] = i['bankName']
                    settlerecord_data['account_number'] = i['cardNo']
                    settlerecord_data['amount'] = i['amt']
                    settlerecord_data['settlement_time'] = settlement_time
                    settlerecord_data['fail_reason'] = i['failReason']
                    settlerecord_data['level_id'] = merchant.level_id
                    settlerecord_data['channel_id'] = 2
                    if i['status'] == '出款成功':
                        settlerecord_data['status'] = 3
                    elif i['status'] == '出款中':
                        settlerecord_data['status'] = 2
                    else:
                        settlerecord_data['status'] = 5
                    instance, create = MerchantSettlementRecord.objects.update_or_create(defaults=settlerecord_data, merchant_id=merchant_id, settlement_time=settlement_time)
    business_no = merchant.wyfMerchantID
    s_tranDates = datetime.datetime.strptime(start_time, "%Y-%m-%d")
    s_tranDatee = datetime.datetime.strptime(end_time, "%Y-%m-%d")
    while True:
        if s_tranDates > s_tranDatee:
            break
        if user_id not in [1, 9300, 65139, 49698]:
            instance = MerchantSettlementRecord.objects.filter(merchant_id=merchant_id, settlement_time=f'{s_tranDates.strftime("%Y-%m-%d 00:00:00")}').first()
            if instance:
                s_tranDates = s_tranDates + datetime.timedelta(days=1)
                continue
        result = query_sxf_transettle(business_no, s_tranDates.strftime("%Y-%m-%d"))
        if result:
            for i in result:
                if i['status'] == '付款成功':
                    # url = 'https://manage.gdwxyf.com/internal/searchorders.do'
                    # body = {
                    #     'shopId': business_no,
                    #     'channel': 'sxf_tq',
                    #     'start': (s_tranDates + datetime.timedelta(days=-1)).strftime("%Y-%m-%d 00:00:00"),
                    #     'end': (s_tranDates + datetime.timedelta(days=-1)).strftime("%Y-%m-%d 23:59:59"),
                    #     'orderStatus': '1',
                    #     'orderType': 'all',
                    #     'payType': 'all',
                    #     'agentId': 'all',
                    #     'branchId': '2',
                    #     'account': 'gdyouzheng'
                    # }
                    # response = requests.post(url=url, data=body).json()
                    # if i['paidout'] == response['statistics']['paidoutMoney'] or (i['paidout']+i['commission']) == response['statistics']['paidoutMoney']:
                    #     continue
                    settlement_time = datetime.datetime.strptime(str(i['date']), "%Y%m%d")
                    settlerecord_data = {}
                    settlerecord_data['merchant_id'] = merchant_id
                    settlerecord_data['account_name'] = i['card_name']
                    settlerecord_data['account_bank'] = '中国邮政储蓄银行总行'
                    settlerecord_data['account_number'] = i['card']
                    settlerecord_data['amount'] = i['money']
                    settlerecord_data['settlement_time'] = settlement_time
                    settlerecord_data['fail_reason'] = ''
                    settlerecord_data['level_id'] = merchant.level_id
                    settlerecord_data['channel_id'] = 3
                    if i['status'] == '付款成功':
                        settlerecord_data['status'] = 3
                    elif i['status'] == '付款中':
                        settlerecord_data['status'] = 2
                    else:
                        settlerecord_data['status'] = 5
                    instance, create = MerchantSettlementRecord.objects.get_or_create(defaults=settlerecord_data, merchant_id=merchant_id, settlement_time=settlement_time, amount=i['money'])
        s_tranDates = s_tranDates + datetime.timedelta(days=1)
    return '导入成功'


def sync_activity_merchant_tool():
    queryset = AliDiscountCoupon.objects.filter(coupon_channel=2, last_update_time__isnull=True)
    # queryset = AliDiscountCoupon.objects.filter(coupon_channel=2, id=140)
    for query in queryset:
        sync_activity_merchant({'id': query.id})


@shared_task
def add_merchant_array(data):
    business_no_array = data['business_no_array']
    for business_no in business_no_array:
        try:
            business_no = str(business_no).replace(' ', '')
            if not business_no.isdigit() and len(business_no) != 10:
                print('请输入10位纯数字微邮付商户号')
                continue
            md5 = hashlib.md5()  # 使用MD5加密模式
            md5.update(str(business_no).encode('utf-8'))  # 将参数字符串传入
            sign = md5.hexdigest()  # 完成加密并转为大写
            url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
            body = {
                'account': 'gdyouzheng',
                'shopId': sign
            }
            reaponse = requests.post(url=url, data=body)
            try:
                reaponse = reaponse.json()
            except:
                continue
            if 'wx_channel_merchant_id' not in reaponse:
                continue
            if reaponse['wx_channel_merchant_id'] == '':
                continue
            merchant = Merchant.objects.filter(wyfMerchantID=str(business_no)).first()
            if merchant:
                merchantchannel_data = {
                    'merchant_id': merchant.id
                }
                if reaponse['allchannelJson']['channel_ruiyinxin']:
                    if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                        merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                        merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                        merchantchannel_data['channel_id'] = 2
                        merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                        MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                if reaponse['allchannelJson']['channel_sxf_tq']:
                    if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                        merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                        merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                        merchantchannel_data['channel_id'] = 3
                        merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                        MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
            else:
                with transaction.atomic():
                    # 创建事务保存点
                    save_id = transaction.savepoint()
                    try:
                        merchant_data = {}
                        channel_mch_id = reaponse['channel_mch_id']
                        channel = Channel.objects.filter(mchid=channel_mch_id).first()
                        if channel:
                            merchant_data['channel_id'] = channel.id
                        merchant_data['name'] = reaponse['shopName']
                        merchant_data['short_name'] = reaponse['shopNickname']
                        merchant_data['wyfMerchantID'] = business_no
                        merchant_data['ruiyinxinMerchantID'] = sign
                        merchant_data['create_time'] = datetime.datetime.now()
                        if reaponse['businessType'] == '1':
                            merchant_data['subject_type'] = 3
                        elif reaponse['businessType'] == '2':
                            merchant_data['subject_type'] = 2
                        elif reaponse['businessType'] == '3':
                            merchant_data['subject_type'] = 1
                        else:
                            merchant_data['subject_type'] = 4
                        level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                        if level:
                            merchant_data['level_id'] = level.id
                        merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                        merchant_data['merchantuser_id'] = merchantuser.id
                        merchant_data['account_name'] = reaponse['cardName']
                        merchant_data['account_bank'] = reaponse['bankName']
                        merchant_data['account_number'] = reaponse['card']
                        if 'latitude' in reaponse:
                            merchant_data['latitude'] = reaponse['latitude']
                        if 'longitude' in reaponse:
                            merchant_data['longitude'] = reaponse['longitude']
                        card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                        card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                        if card_copy_url:
                            merchant_data['card_copy'] = card_copy
                        merchant_data['identification_number'] = reaponse['identity']
                        merchant_data['store_address'] = reaponse['address']
                        store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                        store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                        if store_header_copy_url:
                            merchant_data['store_header_copy'] = store_header_copy
                        merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data, wyfMerchantID=str(business_no))
                        if not merchant.managerName:
                            merchant.managerName = reaponse['keeper']
                        if not merchant.managermobile:
                            merchant.managermobile = reaponse['phone']
                        if not merchant.prov_name:
                            merchant.prov_name = reaponse['province']
                            merchant.city_name = reaponse['city']
                            merchant.district_name = reaponse['area']
                        if not merchant.business_category:
                            merchant.business_category_id = 1
                        if not merchant.store_logo_copy:
                            merchant.store_logo_copy = 'default_merchant_logo.jpg'
                        merchant.save()
                        if cereate and merchant.channel:
                            store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                            store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                            if store_header_copy_url:
                                MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                            store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                            store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                            if store_indoor_copy_url:
                                MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                            merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                            merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                            if merchantCheck_url:
                                MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                        merchantchannel_data = {
                            'merchant_id': merchant.id
                        }
                        if reaponse['allchannelJson']['channel_ruiyinxin']:
                            if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                merchantchannel_data['channel_id'] = 2
                                merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                        if reaponse['allchannelJson']['channel_sxf_tq']:
                            if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                merchantchannel_data['channel_id'] = 3
                                merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                        transaction.savepoint_commit(save_id)
                    except Exception as e:
                        logger.error(e)
                        transaction.savepoint_rollback(save_id)
                        continue
        except:
            pass
    redis_conn = get_redis_connection("stock_mchid")
    redis_conn.delete("is_add_merchant")
    return '添加商户同步成功'


@shared_task
def sync_activity_wyh_data(data):
    record_id = data['record_id']
    record = AliDiscountCouponSyncWYHRecord.objects.get(id=record_id)
    alidiscountcoupon = record.alidiscountcoupon
    remark = ''
    if alidiscountcoupon.coupon_status in [1, 3, 4]:
            activity_status = get_wyhactivity_status(alidiscountcoupon.template_id)
            if activity_status == 'SUBMITED':
                alidiscountcoupon.coupon_status = 3
                alidiscountcoupon.save()
            elif activity_status == 'PENDING':
                alidiscountcoupon.coupon_status = 1
                alidiscountcoupon.save()
            elif activity_status == 'NORMAL':
                alidiscountcoupon.coupon_status = 1
                alidiscountcoupon.save()
    if alidiscountcoupon.distribution_platform_id == 5:
        if alidiscountcoupon.coupon_way in [1, 4]:
            try:
                result = get_level_distribution(alidiscountcoupon.template_id)
                queryset_list = []
                diacountcouponlevel_dict = {}
                level_dict = {}
                level_range = 4
                check_level_name = []
                id_level_name = {}
                allot_id_dict = {}
                diacountcouponlevel_number_dict = {}
                for i in result:
                    level_name = f'{i["province"]}{i["city"]}{i["district"]}{i["net"]}'.replace('None', '')
                    oldwyhlevel = OldWYHLevel.objects.filter(name=level_name).first()
                    if not oldwyhlevel:
                        remark += f'{level_name}机构不存在,'
                        continue
                    level_id = oldwyhlevel.level_id
                    if oldwyhlevel.level.level < level_range:
                        level_range = oldwyhlevel.level.level
                    if level_name not in check_level_name:
                        discount_coupon_level_data = {}
                        discount_coupon_level_data['level_id'] = level_id
                        discount_coupon_level_data['identify'] = i['id']
                        discount_coupon_level_data['activity_id'] = alidiscountcoupon.id
                        discount_coupon_level_data['p_max_number'] = i['total_inventory']
                        discount_coupon_level_data['remain_number'] = i['quantity']
                        discount_coupon_level_data['p_remain_number'] = i['quantity']
                        diacountcouponlevel, create = AliDiscountCouponLevel.objects.update_or_create(defaults=discount_coupon_level_data, activity_id=alidiscountcoupon.id, level_id=level_id)
                        queryset_list.append(diacountcouponlevel.id)
                        diacountcouponlevel_dict[level_name] = diacountcouponlevel.id
                        level_dict[level_name] = level_id
                        check_level_name.append(level_name)
                    else:
                        diacountcouponlevel = AliDiscountCouponLevel.objects.filter(activity_id=alidiscountcoupon.id, level_id=level_id).first()
                        diacountcouponlevel.p_max_number += i['total_inventory']
                        diacountcouponlevel.remain_number += i['quantity']
                        diacountcouponlevel.save()
                    if oldwyhlevel.level.level not in allot_id_dict:
                        allot_id_dict[oldwyhlevel.level.level] = [i['id']]
                    else:
                        allot_id_dict[oldwyhlevel.level.level].append(i['id'])
                    id_level_name[i['id']] = level_name
                level_level_list = []
                queryset_list1 = []
                for i in range(4, level_range, -1):
                    level_level_list.append(i)
                    queryset = AliDiscountCouponLevel.objects.filter(id__in=queryset_list, level__level__in=level_level_list)
                    for query in queryset:
                        query_level = query.level
                        if i == 4:
                            diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 3:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 2:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        else:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 2:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        instance1 = AliDiscountCouponLevel.objects.filter(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id).first()
                        if not instance1:
                            instance1 = AliDiscountCouponLevel.objects.create(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id, max_number=0)
                        queryset_list1.append(instance1.id)
                        relation_data = {
                            "parent_id": instance1.id,
                            "child_id": query.id
                        }
                        AliDiscountCouponLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                level_level_list = []
                for i in range(4, level_range, -1):
                    level_level_list.append(i)
                    queryset = AliDiscountCouponLevel.objects.filter(id__in=queryset_list1, level__level__in=level_level_list)
                    for query in queryset:
                        query_level = query.level
                        if i == 4:
                            diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 3:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 2:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        else:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 2:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        instance1 = AliDiscountCouponLevel.objects.filter(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id).first()
                        if not instance1:
                            instance1 = AliDiscountCouponLevel.objects.create(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id, max_number=0)
                        queryset_list1.append(instance1.id)
                        relation_data = {
                            "parent_id": instance1.id,
                            "child_id": query.id
                        }
                        AliDiscountCouponLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                allot_id_list = allot_id_dict[sorted(allot_id_dict.keys())[0]]
                check_activity_data_list = []
                for allot_id in allot_id_list:
                    check_activity_data_list += get_wyhactivity_data(allot_id)
                voucher_id_list = list(AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id=alidiscountcoupon.id).values_list('voucher_id', flat=True))
                update_level_name_dict = {}
                conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                       port=settings.DATABASES['default']['PORT'],
                                       database=settings.DATABASES['default']['NAME'],
                                       user=settings.DATABASES['default']['USER'],
                                       password=settings.DATABASES['default']['PASSWORD'])
                cursor = conn.cursor()
                try:
                    sql = f'''INSERT INTO tb_ali_discount_coupon_info(alidiscountcoupon_id,alidiscountcoupon_level_id,voucher_id,name,status,gmt_use,gmt_active,gmt_create,gmt_distributed,template_id,mobile,companyuser_id,level_id) VALUES '''
                    a = 0
                    for activity_data in check_activity_data_list:
                        level_name = f'{activity_data["marketer_province"]}{activity_data["marketer_city"]}{activity_data["marketer_district"]}{activity_data["marketer_net"]}'.replace('None', '')
                        if level_name not in level_dict:
                            begin_level_name = level_name
                            if begin_level_name in update_level_name_dict:
                                level_name = update_level_name_dict[begin_level_name]
                            else:
                                allot_id = update_wyhactivity_data_level(allot_id_dict[sorted(allot_id_dict.keys())[-1]], activity_data['marketer_tel'])
                                if not allot_id:
                                    record.sync_status = 4
                                    record.finish_time = datetime.datetime.now()
                                    record.remark = f'同步微邮惠派发明细失败,失败原因:{level_name}层级不在分配中,客户经理:{activity_data["marketer_name"]},手机号:{activity_data["marketer_tel"]}'
                                    record.save()
                                    alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                                    alidiscountcoupon.save()
                                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                                    if re_record:
                                        re_record.sync_status = 1
                                        re_record.save()
                                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                                    conn.rollback()
                                    cursor.close()
                                    conn.close()
                                    return '同步微邮惠派发明细失败'
                                else:
                                    level_name = id_level_name[allot_id]
                                    update_level_name_dict[begin_level_name] = level_name
                        if activity_data['wx_coupon_id']:
                            if activity_data['wx_coupon_id'] not in voucher_id_list:
                                marketer_name = activity_data['marketer_name']
                                marketer_tel = activity_data['marketer_tel']
                                marketer = User.objects.filter(mobile=marketer_tel).first()
                                if not marketer:
                                    marketer = User.objects.create(
                                        mobile=marketer_tel,
                                        username=marketer_name,
                                        nickname=marketer_name,
                                        role_id=3,
                                        level_id=level_dict[level_name],
                                        create_time=datetime.datetime.now()
                                    )
                                insert_data = []
                                insert_data.append(alidiscountcoupon.id)
                                insert_data.append(diacountcouponlevel_dict[level_name])
                                insert_data.append(activity_data['wx_coupon_id'])
                                insert_data.append(activity_data['card_name'])
                                if activity_data['status'] == True:
                                    if activity_data['verify_status'] == 'USED':
                                        insert_data.append(7)
                                        insert_data.append(activity_data['used_at'])
                                        insert_data.append(activity_data['received_at'])
                                        insert_data.append(activity_data['received_at'])
                                        insert_data.append(activity_data['received_at'])
                                    else:
                                        insert_data.append(1)
                                        insert_data.append('null')
                                        insert_data.append(activity_data['received_at'])
                                        insert_data.append(activity_data['received_at'])
                                        insert_data.append(activity_data['received_at'])
                                elif activity_data['status'] == False:
                                    insert_data.append(0)
                                    insert_data.append('null')
                                    insert_data.append('null')
                                    insert_data.append('null')
                                    insert_data.append('null')
                                else:
                                    insert_data.append(14)
                                    insert_data.append('null')
                                    insert_data.append('null')
                                    insert_data.append('null')
                                    insert_data.append('null')
                                insert_data.append(alidiscountcoupon.template_id)
                                insert_data.append(activity_data['tel'])
                                insert_data.append(marketer.id)
                                insert_data.append(level_dict[level_name])
                                sql += str(tuple(insert_data)).replace("'null'", 'null').replace("None", 'null')
                                sql += ','
                                a = 1
                        else:
                            if diacountcouponlevel_dict[level_name] not in diacountcouponlevel_number_dict:
                                diacountcouponlevel_number_dict[diacountcouponlevel_dict[level_name]] = 0
                            else:
                                diacountcouponlevel_number_dict[diacountcouponlevel_dict[level_name]] += 1
                    sql = sql[:-1]
                    sql += ';'
                    if a == 1:
                        cursor.execute(sql)
                        conn.commit()
                except Exception as e:
                    print(e)
                    record.sync_status = 4
                    record.finish_time = datetime.datetime.now()
                    record.remark = f'同步微邮惠派发明细失败,失败原因:{e}'
                    record.save()
                    alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                    alidiscountcoupon.save()
                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                    if re_record:
                        re_record.sync_status = 1
                        re_record.save()
                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                    conn.rollback()
                    cursor.close()
                    conn.close()
                    return '同步微邮惠派发明细失败'
                sql = f'''SELECT alidiscountcoupon_level_id,COUNT(id) AS number_num FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (1,7) GROUP BY alidiscountcoupon_level_id;'''
                cursor.execute(sql)
                rows = cursor.fetchall()
                dict_info = {}
                for row in rows:
                    dict_info[row[0]] = {}
                    dict_info[row[0]]['take_number'] = row[1]
                sql = f'''SELECT alidiscountcoupon_level_id,COUNT(id) AS number_num FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (7) GROUP BY alidiscountcoupon_level_id;'''
                cursor.execute(sql)
                rows = cursor.fetchall()
                for row in rows:
                    dict_info[row[0]]['use_number'] = row[1]
                for key, value in dict_info.items():
                    cashactivitylevel = AliDiscountCouponLevel.objects.filter(id=key).update(
                        take_number=value['take_number'],
                        receive_number=value['take_number'],
                        use_number=value['use_number'] if 'use_number' in value else 0,
                        remain_number=F('remain_number') + diacountcouponlevel_number_dict[key] if key in diacountcouponlevel_number_dict else 0
                    )
                level_list = ['prov_id', 'city_id', 'district_id', 'branch_id']
                for level_str in level_list:
                    sql = f'''SELECT {level_str},COUNT(voucher_id),COUNT(status=7 OR NULL) AS use__sum FROM (SELECT * FROM (SELECT voucher_id,level_id,status FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (1,7)) AS a LEFT JOIN tb_u_level AS b ON a.level_id=b.id) AS c GROUP BY {level_str};'''
                    cursor.execute(sql)
                    rows = cursor.fetchall()
                    for row in rows:
                        if level_str == 'prov_id':
                            AliDiscountCoupon.objects.filter(id=alidiscountcoupon.id).update(
                                send_count=row[1],
                                use_count=row[2],
                                remain_number=F('max_number') - row[1],
                            )
                        cashactivitylevel = AliDiscountCouponLevel.objects.filter(level_id=row[0], activity_id=alidiscountcoupon.id).update(
                            p_take_number=row[1],
                            p_receive_number=row[1],
                            p_use_number=row[2],
                            p_remain_number=F('p_max_number') - row[1]
                        )
                cursor.close()
                conn.close()
                record.finish_time = datetime.datetime.now()
                record.sync_status = 3
                record.remark = remark
                record.save()
                if alidiscountcoupon.coupon_status == 2:
                    use_amount = alidiscountcoupon.use_count * alidiscountcoupon.amount
                    alidiscountcoupon.use_amount = use_amount
                alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                alidiscountcoupon.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细成功'
            except Exception as e:
                record.sync_status = 4
                record.finish_time = datetime.datetime.now()
                remark += f'同步微邮惠派发明细失败, 失败原因:{e}'
                record.remark = remark
                record.save()
                alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                alidiscountcoupon.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细失败'
        elif alidiscountcoupon.coupon_way in [2, 7]:
            try:
                relevance = AliDiscountCouponWYHRelevance.objects.filter(template_id=alidiscountcoupon.template_id)
                if not relevance:
                    record.sync_status = 4
                    record.finish_time = datetime.datetime.now()
                    record.remark = f'同步微邮惠派发明细失败, 失败原因:批次id尚未关联微邮惠活动'
                    record.save()
                    sync_activity_wyh_relevance.delay()
                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                    if re_record:
                        re_record.sync_status = 1
                        re_record.save()
                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                    return '同步微邮惠派发明细失败'
                relevance_id_list = list(relevance.values_list('activity_id', flat=True))
                template_id_list = list(AliDiscountCouponWYHRelevance.objects.filter(activity_id__in=relevance_id_list).values_list('template_id', flat=True))
                alidiscountcoupon_set = AliDiscountCoupon.objects.filter(template_id__in=template_id_list)
                alidiscountcoupon_id_dict = {}
                for alidiscountcoupon in alidiscountcoupon_set:
                    alidiscountcoupon_id_dict[alidiscountcoupon.template_id] = alidiscountcoupon.id
                alidiscountcoupon_id_list = list(alidiscountcoupon_set.values_list('id', flat=True))
                register_id_list = list(AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id__in=alidiscountcoupon_id_list).values_list('out_biz_no', flat=True))
                check_activity_data_list = get_wyhactivity_csvdata(register_id_list, relevance_id_list)
                conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                       port=settings.DATABASES['default']['PORT'],
                                       database=settings.DATABASES['default']['NAME'],
                                       user=settings.DATABASES['default']['USER'],
                                       password=settings.DATABASES['default']['PASSWORD'])
                cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
                try:
                    sql = f'''INSERT INTO tb_ali_discount_coupon_info(alidiscountcoupon_id,voucher_id,name,status,gmt_use,gmt_active,gmt_create,gmt_distributed,template_id,user_id,out_biz_no) VALUES '''
                    a = 0
                    for activity_data in check_activity_data_list:
                        if activity_data['wx_coupon_id']:
                            insert_data = []
                            if activity_data['coupon_stock_id'] not in alidiscountcoupon_id_dict:
                                alidiscountcoupon1 = AliDiscountCoupon.objects.filter(template_id=activity_data['coupon_stock_id']).first()
                                alidiscountcoupon_id_dict[activity_data['coupon_stock_id']] = alidiscountcoupon1.id
                                AliDiscountCouponWYHRelevance.objects.get_or_create(defaults={
                                    "template_id": activity_data['coupon_stock_id'],
                                    "activity_id": activity_data['activity_id'],
                                    "identification": f"{activity_data['coupon_stock_id']}{activity_data['activity_id']}"
                                },identification=f"{activity_data['coupon_stock_id']}{activity_data['activity_id']}")
                            insert_data.append(alidiscountcoupon_id_dict[activity_data['coupon_stock_id']])
                            insert_data.append(activity_data['wx_coupon_id'])
                            insert_data.append(activity_data['coupon_name'])
                            if activity_data['consume_at']:
                                insert_data.append(7)
                                insert_data.append(activity_data['consume_at'])
                                insert_data.append(activity_data['received_at'])
                                insert_data.append(activity_data['received_at'])
                                insert_data.append(activity_data['received_at'])
                            else:
                                insert_data.append(1)
                                insert_data.append('null')
                                insert_data.append(activity_data['received_at'])
                                insert_data.append(activity_data['received_at'])
                                insert_data.append(activity_data['received_at'])
                            insert_data.append(activity_data['coupon_stock_id'])
                            insert_data.append(activity_data['wx_mini_openid'])
                            insert_data.append(activity_data['register_id'])
                            sql += str(tuple(insert_data)).replace("'null'", 'null')
                            sql += ','
                            a = 1
                    sql = sql[:-1]
                    sql += ';'
                    if a == 1:
                        cursor.execute(sql)
                        conn.commit()
                except Exception as e:
                    print(e)
                    record.sync_status = 4
                    record.finish_time = datetime.datetime.now()
                    record.remark = f'同步微邮惠派发明细失败,失败原因:{e}'
                    record.save()
                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                    if re_record:
                        re_record.sync_status = 1
                        re_record.save()
                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                    conn.rollback()
                    cursor.close()
                    conn.close()
                    return '同步微邮惠派发明细失败'
                for key,value in alidiscountcoupon_id_dict.items():
                    sql = f'''SELECT COUNT(status=7 OR NULL) AS use_number,COUNT(id) AS take_number FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={value} AND status IN (1,7);'''
                    cursor.execute(sql)
                    row = cursor.fetchone()
                    use_number = row['use_number'] if 'use_number' in row else 0
                    take_number = row['take_number'] if 'take_number' in row else 0
                    AliDiscountCoupon.objects.filter(id=value).update(
                        send_count=take_number,
                        use_count=use_number,
                        remain_number=F('max_number') - take_number,
                    )
                cursor.close()
                conn.close()
                record.finish_time = datetime.datetime.now()
                record.sync_status = 3
                record.remark = remark
                record.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细成功'
            except Exception as e:
                record.sync_status = 4
                record.finish_time = datetime.datetime.now()
                record.remark = f'同步微邮惠派发明细失败, 失败原因:{e}'
                record.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细失败'
    elif alidiscountcoupon.distribution_platform_id == 6:
        if alidiscountcoupon.coupon_way in [1, 4]:
            try:
                level_range = 4
                queryset_list = []
                level_dict = {}
                diacountcouponlevel_dict = {}
                result = get_ylx_level_distribution(alidiscountcoupon.template_id)
                for i in result:
                    orgId = i['orgId']
                    beAllotNum = int(i['beAllotNum'])
                    beRecycleNum = int(i['beRecycleNum'])
                    allotNum = int(i['allotNum'])
                    recycleNum = int(i['recycleNum'])
                    num = int(i['num'])
                    p_max_number = beAllotNum - beRecycleNum
                    max_number = beAllotNum - beRecycleNum + recycleNum - allotNum
                    level = Level.objects.filter(orgId=orgId).first()
                    if not level:
                        remark += f'{orgId}机构不存在,'
                        continue
                    if level.level < level_range:
                        level_range = level.level
                    if max_number > 0:
                        discount_coupon_level_data = {}
                        discount_coupon_level_data['level_id'] = level.id
                        # discount_coupon_level_data['identify'] = i['id']
                        discount_coupon_level_data['activity_id'] = alidiscountcoupon.id
                        discount_coupon_level_data['p_max_number'] = p_max_number
                        discount_coupon_level_data['max_number'] = max_number
                        discount_coupon_level_data['remain_number'] = num
                        diacountcouponlevel, create = AliDiscountCouponLevel.objects.update_or_create(defaults=discount_coupon_level_data, activity_id=alidiscountcoupon.id, level_id=level.id)
                        queryset_list.append(diacountcouponlevel.id)
                        diacountcouponlevel_dict[level.account_nine] = diacountcouponlevel.id
                        level_dict[level.account_nine] = level.id
                        # check_level_name.append(level_name)
                level_level_list = []
                queryset_list1 = []
                for i in range(4, level_range, -1):
                    level_level_list.append(i)
                    queryset = AliDiscountCouponLevel.objects.filter(id__in=queryset_list, level__level__in=level_level_list)
                    for query in queryset:
                        query_level = query.level
                        if i == 4:
                            diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 3:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 2:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        else:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 2:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        instance1 = AliDiscountCouponLevel.objects.filter(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id).first()
                        if not instance1:
                            instance1 = AliDiscountCouponLevel.objects.create(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id, max_number=0)
                        queryset_list1.append(instance1.id)
                        relation_data = {
                            "parent_id": instance1.id,
                            "child_id": query.id
                        }
                        AliDiscountCouponLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                level_level_list = []
                for i in range(4, level_range, -1):
                    level_level_list.append(i)
                    queryset = AliDiscountCouponLevel.objects.filter(id__in=queryset_list1, level__level__in=level_level_list)
                    for query in queryset:
                        query_level = query.level
                        if i == 4:
                            diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 3:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        elif i == 2:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        else:
                            if query_level.level == 4:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent.parent_id
                            elif query_level.level == 3:
                                diacountcouponlevel_level_id = query_level.parent.parent.parent_id
                            elif query_level.level == 2:
                                diacountcouponlevel_level_id = query_level.parent.parent_id
                            else:
                                diacountcouponlevel_level_id = query_level.parent_id
                        instance1 = AliDiscountCouponLevel.objects.filter(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id).first()
                        if not instance1:
                            instance1 = AliDiscountCouponLevel.objects.create(level_id=diacountcouponlevel_level_id, activity_id=alidiscountcoupon.id, max_number=0)
                        queryset_list1.append(instance1.id)
                        relation_data = {
                            "parent_id": instance1.id,
                            "child_id": query.id
                        }
                        AliDiscountCouponLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                queryset = AliDiscountCouponLevel.objects.filter(id__in=queryset_list1)
                for query in queryset:
                    alidiscountcoupon_id_list1 = list(AliDiscountCouponLevelRelation.objects.filter(parent_id=query.id).values_list('child_id',flat=True))
                    query_max_number = AliDiscountCouponLevel.objects.filter(id__in=alidiscountcoupon_id_list1).aggregate(Sum('max_number'))
                    if query_max_number:
                        query_max_number = query_max_number['max_number__sum']
                        query.p_max_number = query.max_number + query_max_number
                        query.save()
                    else:
                        query.p_max_number = query.max_number
                        query.save()
                check_activity_data_list = get_ylxactivity_data(alidiscountcoupon.template_id)
                voucher_id_list = list(AliDiscountCouponInfo.objects.filter(alidiscountcoupon_id=alidiscountcoupon.id).values_list('voucher_id', flat=True))
                conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                       port=settings.DATABASES['default']['PORT'],
                                       database=settings.DATABASES['default']['NAME'],
                                       user=settings.DATABASES['default']['USER'],
                                       password=settings.DATABASES['default']['PASSWORD'])
                cursor = conn.cursor()
                try:
                    sql = f'''INSERT INTO tb_ali_discount_coupon_info(alidiscountcoupon_id,alidiscountcoupon_level_id,voucher_id,name,status,gmt_use,gmt_active,gmt_create,gmt_distributed,template_id,mobile,companyuser_id,level_id,customer_user_id) VALUES '''
                    a = 0
                    for activity_data in check_activity_data_list:
                        activity_data = activity_data.split(',')
                        account_nine = activity_data[17]
                        wx_coupon_id = activity_data[5].replace("'", '')
                        marketer_name = activity_data[14]
                        marketer_tel = activity_data[15]
                        mobile = activity_data[11]
                        idcode = activity_data[12].replace("'", '')
                        username = activity_data[10]
                        card_name = activity_data[4]
                        received_at = activity_data[9]
                        used_at = activity_data[9]
                        old_openid = activity_data[1]
                        if wx_coupon_id not in voucher_id_list:
                            marketer = User.objects.filter(mobile=marketer_tel).first()
                            if not marketer:
                                marketer = User.objects.create(
                                    mobile=marketer_tel,
                                    username=marketer_name,
                                    nickname=marketer_name,
                                    role_id=3,
                                    level_id=level_dict[account_nine],
                                    create_time=datetime.datetime.now()
                                )
                            customer_user = CustomerUser.objects.filter(mobile=mobile).first()
                            if not customer_user:
                                customer_user = CustomerUser.objects.create(
                                    mobile=mobile,
                                    username=username,
                                    old_openid=old_openid,
                                    idcode=idcode,
                                    level_id=level_dict[account_nine],
                                    realname=username,
                                    is_offline=0,
                                    is_distributor=0,
                                    user_id=marketer.id,
                                    is_auth=2,
                                    create_time=datetime.datetime.now(),
                                )
                            insert_data = []
                            insert_data.append(alidiscountcoupon.id)
                            insert_data.append(diacountcouponlevel_dict[account_nine])
                            insert_data.append(wx_coupon_id)
                            insert_data.append(card_name)
                            activity_data_status = get_ylxactivity_data_status(mobile, alidiscountcoupon.template_id)
                            if activity_data_status == 'USED':
                                insert_data.append(7)
                                insert_data.append(used_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                            else:
                                insert_data.append(1)
                                insert_data.append('null')
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                            insert_data.append(alidiscountcoupon.template_id)
                            insert_data.append(mobile)
                            insert_data.append(marketer.id)
                            insert_data.append(level_dict[account_nine])
                            insert_data.append(customer_user.id)
                            sql += str(tuple(insert_data)).replace("'null'", 'null').replace("None", 'null')
                            sql += ','
                            a = 1
                    sql = sql[:-1]
                    sql += ';'
                    if a == 1:
                        cursor.execute(sql)
                        conn.commit()
                except Exception as e:
                    print(e)
                    record.sync_status = 4
                    record.finish_time = datetime.datetime.now()
                    record.remark = f'同步微邮惠派发明细失败,失败原因:{e}'
                    record.save()
                    alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                    alidiscountcoupon.save()
                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                    if re_record:
                        re_record.sync_status = 1
                        re_record.save()
                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                    conn.rollback()
                    cursor.close()
                    conn.close()
                    return '同步微邮惠派发明细失败'
                sql = f'''SELECT alidiscountcoupon_level_id,COUNT(id) AS number_num FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (1,7) GROUP BY alidiscountcoupon_level_id;'''
                cursor.execute(sql)
                rows = cursor.fetchall()
                dict_info = {}
                for row in rows:
                    dict_info[row[0]] = {}
                    dict_info[row[0]]['take_number'] = row[1]
                sql = f'''SELECT alidiscountcoupon_level_id,COUNT(id) AS number_num FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (7) GROUP BY alidiscountcoupon_level_id;'''
                cursor.execute(sql)
                rows = cursor.fetchall()
                for row in rows:
                    dict_info[row[0]]['use_number'] = row[1]
                for key, value in dict_info.items():
                    cashactivitylevel = AliDiscountCouponLevel.objects.filter(id=key).update(
                        take_number=value['take_number'],
                        receive_number=value['take_number'],
                        use_number=value['use_number'] if 'use_number' in value else 0,
                        remain_number=F('remain_number')
                    )
                level_list = ['prov_id', 'city_id', 'district_id', 'branch_id']
                for level_str in level_list:
                    sql = f'''SELECT {level_str},COUNT(voucher_id),COUNT(status=7 OR NULL) AS use__sum FROM (SELECT * FROM (SELECT voucher_id,level_id,status FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (1,7)) AS a LEFT JOIN tb_u_level AS b ON a.level_id=b.id) AS c GROUP BY {level_str};'''
                    cursor.execute(sql)
                    rows = cursor.fetchall()
                    for row in rows:
                        if level_str == 'prov_id':
                            AliDiscountCoupon.objects.filter(id=alidiscountcoupon.id).update(
                                send_count=row[1],
                                use_count=row[2],
                                remain_number=F('max_number') - row[1],
                            )
                        cashactivitylevel = AliDiscountCouponLevel.objects.filter(level_id=row[0], activity_id=alidiscountcoupon.id).update(
                            p_take_number=row[1],
                            p_receive_number=row[1],
                            p_use_number=row[2],
                            p_remain_number=F('p_max_number') - row[1]
                        )
                cursor.close()
                conn.close()
                record.finish_time = datetime.datetime.now()
                record.sync_status = 3
                record.remark = remark
                record.save()
                if alidiscountcoupon.coupon_status == 2:
                    use_amount = alidiscountcoupon.use_count * alidiscountcoupon.amount
                    alidiscountcoupon.use_amount = use_amount
                alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                alidiscountcoupon.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细成功'
            except Exception as e:
                record.sync_status = 4
                record.finish_time = datetime.datetime.now()
                remark += f'同步微邮惠派发明细失败, 失败原因:{e}'
                record.remark = remark
                record.save()
                alidiscountcoupon.wyh_last_update_time = datetime.datetime.now()
                alidiscountcoupon.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细失败'

        elif alidiscountcoupon.coupon_way in [2, 7]:
            try:
                check_activity_data_list = get_ylxactivity_publicity_data(alidiscountcoupon.template_id)
                conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                       port=settings.DATABASES['default']['PORT'],
                                       database=settings.DATABASES['default']['NAME'],
                                       user=settings.DATABASES['default']['USER'],
                                       password=settings.DATABASES['default']['PASSWORD'])
                cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
                try:
                    sql = f'''INSERT INTO tb_ali_discount_coupon_info(alidiscountcoupon_id,voucher_id,name,status,gmt_use,gmt_active,gmt_create,gmt_distributed,template_id,user_id,out_biz_no) VALUES '''
                    a = 0
                    for activity_data in check_activity_data_list:
                        activity_data = activity_data.split(',')
                        wx_coupon_id = activity_data[2].replace("'", '')
                        coupon_name = activity_data[3]
                        activity_statue = activity_data[13]
                        received_at = activity_data[14]
                        register_id = activity_data[0].replace("'", '')
                        wx_mini_openid = activity_data[8]
                        if activity_statue in ['已使用', '已领取']:
                            insert_data = []
                            insert_data.append(alidiscountcoupon.id)
                            insert_data.append(wx_coupon_id)
                            insert_data.append(coupon_name)
                            if activity_statue == '已使用':
                                insert_data.append(7)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                            else:
                                insert_data.append(1)
                                insert_data.append('null')
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                                insert_data.append(received_at)
                            insert_data.append(alidiscountcoupon.template_id)
                            insert_data.append(wx_mini_openid)
                            insert_data.append(register_id)
                            sql += str(tuple(insert_data)).replace("'null'", 'null')
                            sql += ','
                            a = 1
                    sql = sql[:-1]
                    sql += ';'
                    if a == 1:
                        cursor.execute(sql)
                        conn.commit()
                except Exception as e:
                    print(e)
                    record.sync_status = 4
                    record.finish_time = datetime.datetime.now()
                    record.remark = f'同步微邮惠派发明细失败,失败原因:{e}'
                    record.save()
                    re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                    if re_record:
                        re_record.sync_status = 1
                        re_record.save()
                        sync_activity_wyh_data.delay({'record_id': re_record.id})
                    conn.rollback()
                    cursor.close()
                    conn.close()
                    return '同步微邮惠派发明细失败'
                sql = f'''SELECT COUNT(status=7 OR NULL) AS use_number,COUNT(id) AS take_number FROM tb_ali_discount_coupon_info WHERE alidiscountcoupon_id={alidiscountcoupon.id} AND status IN (1,7);'''
                cursor.execute(sql)
                row = cursor.fetchone()
                use_number = row['use_number'] if 'use_number' in row else 0
                take_number = row['take_number'] if 'take_number' in row else 0
                AliDiscountCoupon.objects.filter(id=alidiscountcoupon.id).update(
                    send_count=take_number,
                    use_count=use_number,
                    remain_number=F('max_number') - take_number,
                )
                cursor.close()
                conn.close()
                record.finish_time = datetime.datetime.now()
                record.sync_status = 3
                record.remark = remark
                record.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细成功'
            except Exception as e:
                record.sync_status = 4
                record.finish_time = datetime.datetime.now()
                record.remark = f'同步微邮惠派发明细失败, 失败原因:{e}'
                record.save()
                re_record = AliDiscountCouponSyncWYHRecord.objects.filter(sync_status=2).first()
                if re_record:
                    re_record.sync_status = 1
                    re_record.save()
                    sync_activity_wyh_data.delay({'record_id': re_record.id})
                return '同步微邮惠派发明细失败'


@shared_task
def sync_activity_wyh_relevance():
    result = get_wyhactivity_relevance()
    identification_list = list(AliDiscountCouponWYHRelevance.objects.all().values_list('identification',flat=True))
    conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                           port=settings.DATABASES['default']['PORT'],
                           database=settings.DATABASES['default']['NAME'],
                           user=settings.DATABASES['default']['USER'],
                           password=settings.DATABASES['default']['PASSWORD'])
    cursor = conn.cursor()
    try:
        sql = f'''INSERT INTO tb_ali_discount_coupon_wyh_relevance(template_id,activity_id,identification) VALUES '''
        for activity_data in result:
            activity_id = activity_data['id']
            coupons = activity_data['coupons']
            for coupon in coupons:
                identification = f"{coupon['stock_id']}{activity_id}"
                if identification not in identification_list:
                    insert_data = []
                    insert_data.append(coupon['stock_id'])
                    insert_data.append(activity_id)
                    insert_data.append(identification)
                    sql += str(tuple(insert_data)).replace("'null'", 'null')
                    sql += ','
        sql = sql[:-1]
        sql += ';'
        print(sql)
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()
        return '同步微邮惠批次关联失败'
    cursor.close()
    conn.close()
    return '同步微邮惠批次关联成功'


@shared_task
def sync_weiyoufu_trading():
    query_set = MerchantBill.objects.filter(bill_type=2, other_amount=None)
    for query in query_set:
        try:
            yesterday = datetime.datetime.now() + datetime.timedelta(days=-1)
            if query.other_amount == None and query.receipts_time < yesterday:
                url = 'https://manage.gdwxyf.com/internal/searchorders.do'
                body = {
                    "shopId": query.merchant.wyfMerchantID,
                    "start": query.receipts_time.strftime("%Y-%m-%d 00:00:00"),
                    "end": query.receipts_time.strftime("%Y-%m-%d 23:59:59"),
                    "branchId": "2",
                    "account": "gdyouzheng",
                    "orderStatus": "all"
                }
                response = requests.post(url=url, data=body)
                result = response.json()['showList']
                other_amount = Decimal('0')
                other_commission = Decimal('0')
                other_settlement_amount = Decimal('0')
                for i in result:
                    if 'subOpenid' in i:
                        if 'omjwf4' != i['subOpenid'][:6]:
                            other_amount += Decimal(i['totalFee'])
                            other_commission += Decimal(i['commissionPay'])
                            other_settlement_amount += Decimal(i['paidout'])
                    else:
                        if i['device'] != '微邮惠商圈':
                            other_amount += Decimal(i['totalFee'])
                            other_commission += Decimal(i['commissionPay'])
                            other_settlement_amount += Decimal(i['paidout'])
                query.other_amount = other_amount
                query.other_commission = other_commission
                query.other_settlement_amount = other_settlement_amount
                query.save()
        except:
            pass
    return '同步交易记录完成'


@shared_task
def operation_merchant_bank_quest(data):
    id = data['id']
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        operation_merchant_bank_quest.delay({'id': id})
        return '移交其他服务器处理'
    quest = MerchantBankQRCodeQuest.objects.get(id=id)
    if quest.file_type == 1:
        excel_data = []
        excel_data_title = ['员工编码', '子码编码', '员工码下载地址', '状态', '说明']
        excel_data.append(excel_data_title)
        file_contents = requests.get(quest.file.url).content
        wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
        table = wb.sheets()[0]
        title = table.row_values(0)
        nrows = table.nrows
        remark = ''
        success_line = 0
        try:
            business_no_index = title.index('员工编码')
            code_id_index = title.index('子码编码')
            link_index = title.index('员工码下载地址')
            for i in range(1, nrows):
                excel_data_body = []
                rowValues = table.row_values(i)
                code_id = str(rowValues[code_id_index]).replace('.0', '').replace(' ', '')
                business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
                link = str(rowValues[link_index]).replace('.0', '').replace(' ', '')
                excel_data_body.append(business_no)
                excel_data_body.append(code_id)
                excel_data_body.append(link)
                check_qrcode = MerchantBankQRCode.objects.filter(code_id=code_id).first()
                if check_qrcode:
                    excel_data_body.append('失败')
                    excel_data_body.append(f'{code_id}子码编码重复')
                    excel_data.append(excel_data_body)
                    remark += f'{code_id}子码编码重复,'
                    continue
                merchant = Merchant.objects.filter(wyfMerchantID=str(business_no)).first()
                if not merchant:
                    if len(business_no) == 10 and (business_no.startswith('10') or business_no.startswith('60')):
                        md5 = hashlib.md5()
                        md5.update(str(business_no).encode('utf-8'))
                        sign = md5.hexdigest()
                        url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
                        body = {
                            'account': 'gdyouzheng',
                            'shopId': sign
                        }
                        reaponse = requests.post(url=url, data=body)
                        try:
                            reaponse = reaponse.json()
                        except:
                            remark += f'{business_no}商户号添加失败,'
                            excel_data_body.append('失败')
                            excel_data_body.append(f'{business_no}商户号添加失败')
                            excel_data.append(excel_data_body)
                            continue
                        if 'wx_channel_merchant_id' not in reaponse:
                            remark += f'{business_no}商户号错误,'
                            excel_data_body.append('失败')
                            excel_data_body.append(f'{business_no}商户号错误')
                            excel_data.append(excel_data_body)
                            continue
                        if reaponse['wx_channel_merchant_id'] == '':
                            remark += f'{business_no}获取商户信息失败,'
                            excel_data_body.append('失败')
                            excel_data_body.append(f'{business_no}获取商户信息失败')
                            excel_data.append(excel_data_body)
                            continue
                        with transaction.atomic():
                            # 创建事务保存点
                            save_id = transaction.savepoint()
                            try:
                                merchant_data = {}
                                channel_mch_id = reaponse['channel_mch_id']
                                channel = Channel.objects.filter(mchid=channel_mch_id).first()
                                if channel:
                                    merchant_data['channel_id'] = channel.id
                                merchant_data['name'] = reaponse['shopName']
                                merchant_data['short_name'] = reaponse['shopNickname']
                                merchant_data['wyfMerchantID'] = business_no
                                merchant_data['ruiyinxinMerchantID'] = sign
                                merchant_data['create_time'] = datetime.datetime.now()
                                if reaponse['businessType'] == '1':
                                    merchant_data['subject_type'] = 3
                                elif reaponse['businessType'] == '2':
                                    merchant_data['subject_type'] = 2
                                elif reaponse['businessType'] == '3':
                                    merchant_data['subject_type'] = 1
                                else:
                                    merchant_data['subject_type'] = 4
                                level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                                if level:
                                    merchant_data['level_id'] = level.id
                                merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                                merchant_data['merchantuser_id'] = merchantuser.id
                                merchant_data['account_name'] = reaponse['cardName']
                                merchant_data['account_bank'] = reaponse['bankName']
                                merchant_data['account_number'] = reaponse['card']
                                card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                                card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                                if card_copy_url:
                                    merchant_data['card_copy'] = card_copy
                                merchant_data['identification_number'] = reaponse['identity']
                                merchant_data['store_address'] = reaponse['address']
                                store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                                store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                if store_header_copy_url:
                                    merchant_data['store_header_copy'] = store_header_copy
                                merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data,wyfMerchantID=str(business_no))
                                if not merchant.managerName:
                                    merchant.managerName = reaponse['keeper']
                                if not merchant.managermobile:
                                    merchant.managermobile = reaponse['phone']
                                if not merchant.prov_name:
                                    merchant.prov_name = reaponse['province']
                                    merchant.city_name = reaponse['city']
                                    merchant.district_name = reaponse['area']
                                if not merchant.business_category:
                                    merchant.business_category_id = 1
                                if not merchant.store_logo_copy:
                                    merchant.store_logo_copy = 'default_merchant_logo.jpg'
                                merchant.save()
                                if cereate and merchant.channel:
                                    store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                    store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                    if store_header_copy_url:
                                        MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                                    store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                    store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                                    if store_indoor_copy_url:
                                        MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                                    merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                    merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                                    if merchantCheck_url:
                                        MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                                merchantchannel_data = {
                                    'merchant_id': merchant.id
                                }
                                if reaponse['allchannelJson']['channel_ruiyinxin']:
                                    if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                        merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                        merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                        merchantchannel_data['channel_id'] = 2
                                        merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                        MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                              merchant_id=merchant.id,
                                                                              channel_id=merchantchannel_data['channel_id'])
                                if reaponse['allchannelJson']['channel_sxf_tq']:
                                    if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                        merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                        merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                        merchantchannel_data['channel_id'] = 3
                                        merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                        MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                              merchant_id=merchant.id,
                                                                              channel_id=merchantchannel_data['channel_id'])
                                transaction.savepoint_commit(save_id)
                            except Exception as e:
                                print(e)
                                remark += f'{business_no}添加商户信息失败,'
                                excel_data_body.append('失败')
                                excel_data_body.append(f'{business_no}添加商户信息失败')
                                excel_data.append(excel_data_body)
                                continue
                    else:
                        excel_data_body.append('失败')
                        excel_data_body.append(f'{business_no}商户号错误')
                        excel_data.append(excel_data_body)
                        remark += f'{business_no}商户号错误,'
                        continue
                check_qrcode = MerchantBankQRCode.objects.filter(merchant_id=merchant.id).first()
                if check_qrcode:
                    excel_data_body.append('失败')
                    excel_data_body.append(f'{business_no}商户号重复')
                    excel_data.append(excel_data_body)
                    remark += f'{business_no}商户号重复,'
                    continue
                qrcode_data = {}
                qrcode_data['merchant_id'] = merchant.id
                qrcode_data['code_id'] = code_id
                qrcode_data['link'] = link
                qrcode_data['level_id'] = merchant.level_id
                qrcode_data['quest_id'] = quest.id
                qrcode_data['business_no'] = merchant.wyfMerchantID
                qrcode_data['merchant_name'] = merchant.name
                try:
                    url1 = 'https://partner.douyinpay.com/api/merchant/getBankSubQrCodeValue'
                    body1 = {
                        "code_id": code_id,
                        "sign": link.replace(f'https://partner.douyinpay.com/external/bankcode?code_id={code_id}&sign=', '')
                    }
                    response1 = requests.post(url=url1, json=body1)
                    result1 = response1.json()
                    code_url = result1['code_url']
                    parent_code_id = result1['parent_code_id']
                    qrcode = make_qrcode5(code_id, code_url, None)
                    qrcode_data['qrcode'] = qrcode
                    qrcode_data['parent_code_id'] = parent_code_id
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append(f'{business_no}银行码连接有误')
                    excel_data.append(excel_data_body)
                    remark += f'{business_no}银行码连接有误,'
                    continue
                MerchantBankQRCode.objects.get_or_create(code_id=code_id, defaults=qrcode_data)
                excel_data_body.append('成功')
                excel_data_body.append('')
                excel_data.append(excel_data_body)
                success_line += 1
            book_name_xlsx = quest.file.name.split('.')[0] + '_日志.xlsx'
            sheet_name_xlsx = 'json1'
            file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
            write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
            with open(file_path, 'rb') as recode_f:
                file_contents = recode_f.read()
            file = pic_upload(file_contents, book_name_xlsx)
            os.remove(file_path)
            quest.logs_file = file
            quest.success_line = success_line
            quest.error_line = nrows - success_line - 1
            quest.remark = remark
            quest.file_status = 3
            quest.save()
        except:
            quest.file_status = 4
            quest.save()
    elif quest.file_type == 3:
        excel_data = []
        excel_data_title = ['子码编码', '日期', '当日新增银行首绑量', '当日引导绑卡数', '状态', '说明']
        excel_data.append(excel_data_title)
        file_contents = requests.get(quest.file.url).content
        wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
        table = wb.sheets()[0]
        title = table.row_values(0)
        nrows = table.nrows
        remark = ''
        success_line = 0
        try:
            code_id_index = title.index('子码编码')
            bind_time_index = title.index('日期')
            number_index = title.index('当日引导绑卡数')
            first_number_index = title.index('当日新增银行首绑量')
            for i in range(1, nrows):
                excel_data_body = []
                rowValues = table.row_values(i)
                code_id = str(rowValues[code_id_index]).replace('.0', '').replace(' ', '')
                bind_time = str(rowValues[bind_time_index])
                first_number = str(rowValues[first_number_index]).replace('.0', '').replace(' ', '')
                number = str(rowValues[number_index]).replace('.0', '').replace(' ', '')
                excel_data_body.append(code_id)
                excel_data_body.append(bind_time)
                excel_data_body.append(first_number)
                excel_data_body.append(number)
                try:
                    bind_time = datetime.datetime.strptime(bind_time, '%Y-%m-%d')
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append('时间格式有误')
                    excel_data.append(excel_data_body)
                    continue
                qrcode = MerchantBankQRCode.objects.filter(code_id=code_id).first()
                if not qrcode:
                    excel_data_body.append('失败')
                    excel_data_body.append('银行码暂未入库')
                    excel_data.append(excel_data_body)
                    continue
                try:
                    first_number = int(first_number)
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append('当日新增银行首绑量数据有误')
                    excel_data.append(excel_data_body)
                    continue
                try:
                    number = int(number)
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append('当日引导绑卡数数据有误')
                    excel_data.append(excel_data_body)
                    continue
                check_statistics = MErchantBankQPCodeStatistics.objects.filter(code_id=code_id, bind_time=bind_time)
                if check_statistics:
                    excel_data_body.append('失败')
                    excel_data_body.append('子码编码记录重复上传')
                    excel_data.append(excel_data_body)
                    continue
                qrcode_data = {}
                qrcode_data['merchant_id'] = qrcode.merchant_id
                qrcode_data['code_id'] = code_id
                qrcode_data['level_id'] = qrcode.level_id
                qrcode_data['bind_time'] = bind_time
                qrcode_data['bank_qrcode_id'] = qrcode.id
                qrcode_data['quest_id'] = quest.id
                qrcode_data['business_no'] = qrcode.business_no
                qrcode_data['merchant_name'] = qrcode.merchant.name
                qrcode_data['total_number'] = number
                qrcode_data['total_first_number'] = first_number
                instance, create = MErchantBankQPCodeStatistics.objects.get_or_create(defaults=qrcode_data, bind_time=bind_time, code_id=code_id)
                if create:
                    excel_data_body.append('成功')
                    excel_data_body.append('')
                    excel_data.append(excel_data_body)
                    success_line += 1
                    qrcode.total_number += number
                    qrcode.total_first_number += first_number
                    qrcode.save()
                else:
                    excel_data_body.append('失败')
                    excel_data_body.append('同一时间记录重复')
                    excel_data.append(excel_data_body)
            book_name_xlsx = quest.file.name.split('.')[0] + '_日志.xlsx'
            sheet_name_xlsx = 'json1'
            file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
            write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
            with open(file_path, 'rb') as recode_f:
                file_contents = recode_f.read()
            file = pic_upload(file_contents, book_name_xlsx)
            os.remove(file_path)
            quest.logs_file = file
            quest.success_line = success_line
            quest.error_line = nrows - success_line - 1
            quest.remark = remark
            quest.file_status = 3
            quest.save()
        except:
            quest.file_status = 4
            quest.save()
    else:
        excel_data = []
        excel_data_title = ['子码编码', '绑卡时间', '绑卡银行卡号', '绑卡类型' '是否银行首绑', '状态', '说明']
        excel_data.append(excel_data_title)
        file_contents = requests.get(quest.file.url).content
        wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
        table = wb.sheets()[0]
        title = table.row_values(0)
        nrows = table.nrows
        remark = ''
        success_line = 0
        try:
            code_id_index = title.index('子码编码')
            card_number_index = title.index('绑卡银行卡号')
            bind_time_index = title.index('绑卡时间')
            is_first_index = title.index('是否银行首绑')
            card_type_index = title.index('绑卡类型')
            for i in range(1, nrows):
                excel_data_body = []
                rowValues = table.row_values(i)
                code_id = str(rowValues[code_id_index]).replace('.0', '').replace(' ', '')
                card_number = str(rowValues[card_number_index]).replace('.0', '').replace(' ', '')
                bind_time = str(rowValues[bind_time_index])
                is_first = str(rowValues[is_first_index]).replace('.0', '').replace(' ', '')
                card_type = str(rowValues[card_type_index]).replace('.0', '').replace(' ', '')
                excel_data_body.append(code_id)
                excel_data_body.append(bind_time)
                excel_data_body.append(card_number)
                excel_data_body.append(card_type)
                excel_data_body.append(is_first)
                try:
                    bind_time = datetime.datetime.strptime(bind_time, '%Y-%m-%d %H:%M:%S')
                except:
                    excel_data_body.append('失败')
                    excel_data_body.append('时间格式有误')
                    excel_data.append(excel_data_body)
                    continue
                qrcode = MerchantBankQRCode.objects.filter(code_id=code_id).first()
                if not qrcode:
                    excel_data_body.append('失败')
                    excel_data_body.append('银行码暂未入库')
                    excel_data.append(excel_data_body)
                    continue
                if is_first == 'TRUE':
                    is_first1 = 1
                elif is_first == 'FALSE':
                    is_first1 = 0
                else:
                    excel_data_body.append('失败')
                    excel_data_body.append('是否首绑数据有误')
                    excel_data.append(excel_data_body)
                    continue
                if card_type == '储蓄卡':
                    card_type1 = 1
                elif card_type == '信用卡':
                    card_type1 = 2
                else:
                    excel_data_body.append('失败')
                    excel_data_body.append('绑卡类型数据有误')
                    excel_data.append(excel_data_body)
                    continue
                qrcode_data = {}
                qrcode_data['merchant_id'] = qrcode.merchant_id
                qrcode_data['code_id'] = code_id
                qrcode_data['card_number'] = card_number
                qrcode_data['level_id'] = qrcode.level_id
                qrcode_data['is_first'] = is_first1
                qrcode_data['card_type'] = card_type1
                qrcode_data['bind_time'] = bind_time
                qrcode_data['bank_qrcode_id'] = qrcode.id
                qrcode_data['quest_id'] = quest.id
                qrcode_data['business_no'] = qrcode.business_no
                qrcode_data['merchant_name'] = qrcode.merchant.name
                instance, create = MerchantBankQRCodeBindData.objects.get_or_create(defaults=qrcode_data, bind_time=bind_time, code_id=code_id, card_number=card_number)
                if create:
                    excel_data_body.append('成功')
                    excel_data_body.append('')
                    excel_data.append(excel_data_body)
                    success_line += 1
                else:
                    excel_data_body.append('失败')
                    excel_data_body.append('同一时间记录重复')
                    excel_data.append(excel_data_body)
            book_name_xlsx = quest.file.name.split('.')[0] + '_日志.xlsx'
            sheet_name_xlsx = 'json1'
            file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
            write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
            with open(file_path, 'rb') as recode_f:
                file_contents = recode_f.read()
            file = pic_upload(file_contents, book_name_xlsx)
            os.remove(file_path)
            quest.logs_file = file
            quest.success_line = success_line
            quest.error_line = nrows - success_line - 1
            quest.remark = remark
            quest.file_status = 3
            quest.save()
        except:
            quest.file_status = 4
            quest.save()
    re_history = MerchantBankQRCodeQuest.objects.filter(file_status=1).first()
    if re_history:
        re_history.file_status = 2
        re_history.save()
        operation_merchant_bank_quest.delay({'id': re_history.id})


@shared_task
def group_order_resutl_send(data):
    '''小程序成功模板推送'''
    redis_conn = get_redis_connection("small_token")
    access_token = redis_conn.get(settings.CUSTOMER_APP_ID_WYH)
    if not access_token:
        small_url = f'https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={settings.CUSTOMER_APP_ID_WYH}&secret={settings.CUSTOMER_APP_SECRET_WYH}'
        small_reaponse = requests.get(small_url)
        small_data = small_reaponse.json()
        access_token = small_data['access_token']
        redis_conn.setex(settings.CUSTOMER_APP_ID_WYH, small_data['expires_in'], access_token)
    else:
        access_token = access_token.decode()
    openid = data['openid']
    order_id = data['order_id']
    group_state = data['group_state']
    if group_state == 1:
        thing1 = '恭喜您！拼团成功'
        thing2 = '可点击下方查看详情'
    else:
        thing1 = '很遗憾，拼团失败'
        thing2 = '订单超时未成团'
    small_body = {
        "touser": openid,
        "template_id": "JxAYBbdbikTy8AyWgbeS_8P58h8AVQjnv48ZWFEYBAs",
        "page": f"/pages/order_detail/order_detail?id={order_id}",
        "miniprogram_state": "formal",
        "lang": "zh_CN",
        "data": {
            "thing1": {
                "value": thing1
            },
            "thing2": {
                "value": thing2
            }
        }
    }
    small_url1 = f'https://api.weixin.qq.com/cgi-bin/message/subscribe/send?access_token={access_token}'
    response = requests.post(small_url1, data=json.dumps(small_body), headers={'Content-Type': 'application/json'})
    print(response.json())
    return '无绑定openid'


@shared_task
def operation_merchant_enter_quest(data):
    id = data['id']
    s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
    s.connect(("8.8.8.8", 80))
    ip = s.getsockname()[0]
    if ip == '172.16.0.14':
        operation_merchant_enter_quest.delay({'id': id})
        return '移交其他服务器处理'
    quest = MerchantEnterQuest.objects.get(id=id)
    excel_data = []
    excel_data_title = ['商户号', '机构号', '负责人姓名', '负责人手机号码', '状态', '说明']
    excel_data.append(excel_data_title)
    file_contents = requests.get(quest.file.url).content
    wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
    table = wb.sheets()[0]
    title = table.row_values(0)
    nrows = table.nrows
    remark = ''
    success_line = 0
    try:
        business_no_index = title.index('商户号')
        account_index = title.index('机构号')
        managerName_index = title.index('负责人姓名')
        managermobile_index = title.index('负责人手机号码')
        for i in range(1, nrows):
            excel_data_body = []
            rowValues = table.row_values(i)
            business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
            account = str(rowValues[account_index]).replace('.0', '').replace(' ', '')
            managerName = str(rowValues[managerName_index])
            managermobile = str(rowValues[managermobile_index]).replace('.0', '').replace(' ', '')
            excel_data_body.append(business_no)
            excel_data_body.append(account)
            excel_data_body.append(managerName)
            excel_data_body.append(managermobile)
            merchant = Merchant.objects.filter(wyfMerchantID=str(business_no)).first()
            if not merchant:
                excel_data_body.append('失败')
                excel_data_body.append('商户号不存在')
                excel_data.append(excel_data_body)
                remark += f'{business_no}商户号不存在,'
                continue
            level = Level.objects.filter(account=account).first()
            if not level:
                excel_data_body.append('失败')
                excel_data_body.append('机构号不存在')
                excel_data.append(excel_data_body)
                remark += f'{account}机构号不存在,'
                continue
            if not re.match(r'^1[3-9]\d{9}$', managermobile):
                excel_data_body.append('失败')
                excel_data_body.append('联系人手机号格式有误')
                excel_data.append(excel_data_body)
                remark += f'{managermobile}联系人手机号格式有误,'
                continue
            enter_data = {}
            enter_data['level_id'] = level.id
            enter_data['managerName'] = managerName
            enter_data['managermobile'] = managermobile
            enter_data['is_online'] = 2
            enter_data['merchant_id'] = merchant.id
            enter_data['user_id'] = quest.create_user_id
            enter_data['create_time'] = quest.create_time
            MerchantEnter.objects.update_or_create(defaults=enter_data, level_id=enter_data['level_id'], merchant_id=enter_data['merchant_id'])
            success_line += 1
        book_name_xlsx = quest.file.name.split('.')[0] + '_日志.xlsx'
        sheet_name_xlsx = 'json1'
        file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
        write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
        with open(file_path, 'rb') as recode_f:
            file_contents = recode_f.read()
        file = pic_upload(file_contents, book_name_xlsx)
        os.remove(file_path)
        quest.logs_file = file
        quest.success_line = success_line
        quest.error_line = nrows - success_line - 1
        quest.remark = remark
        quest.file_status = 3
        quest.save()
    except:
        quest.file_status = 4
        quest.save()
    re_history = MerchantEnterQuest.objects.filter(file_status=1).first()
    if re_history:
        re_history.file_status = 2
        re_history.save()
        operation_merchant_bank_quest.delay({'id': re_history.id})
    return '批量绑定网点成功'


@shared_task
def nfc_device_bounty_quest_subscription_send(data):
    '''小程序成功模板推送'''
    redis_conn = get_redis_connection("small_token")
    access_token = redis_conn.get(settings.CUSTOMER_APP_ID_WYH)
    if not access_token:
        small_url = f'https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={settings.CUSTOMER_APP_ID_WYH}&secret={settings.CUSTOMER_APP_SECRET_WYH}'
        small_reaponse = requests.get(small_url)
        small_data = small_reaponse.json()
        access_token = small_data['access_token']
        redis_conn.setex(settings.CUSTOMER_APP_ID_WYH, small_data['expires_in'], access_token)
    else:
        access_token = access_token.decode()
    quest = NFCDeviceBountyQuest.objects.get(id=data['id'])
    gcd_formula = "6371 * acos(least(greatest(cos(radians(%s)) * cos(radians(latitude)) * cos(radians(longitude) - radians(%s)) + sin(radians(%s)) * sin(radians(latitude)) , -1), 1))"
    sql = RawSQL(gcd_formula, (quest.latitude, quest.longitude, quest.latitude))
    queryset = NFCDeviceBountyQuestSubscription.objects.filter(is_push=0).annotate(distance=sql).filter(distance__lt=5).order_by(F('distance').asc(nulls_last=True))
    for query in queryset:
        query.is_push = 1
        query.quest_id = quest.id
        query.save()
        customeruservice = query.customeruservice
        if customeruservice.wyh_openid:
            small_body = {
                "touser": customeruservice.wyh_openid,
                "template_id": "-eO1zguJRWFM9T6uOy8nZYv_L8LxVsjVbOcfNYd_b5c",
                "page": f"pages/sj_task_management/sj_task_management",
                "miniprogram_state": "formal",
                "lang": "zh_CN",
                "data": {
                    "time2": {
                        "value": quest.create_time.strftime('%Y-%m-%d %H:%M:%S')
                    },
                    "thing4": {
                        "value": f'完成一笔不少于4元的支付宝NFC交易'
                    },
                    "thing8": {
                        "value": quest.device.address[-18:]
                    },
                    "thing28": {
                        "value": f'{quest.amount}元奖励'
                    },
                    "thing24": {
                        "value": '点击详情秒抢任务'
                    }
                }
            }
            small_url1 = f'https://api.weixin.qq.com/cgi-bin/message/subscribe/send?access_token={access_token}'
            response = requests.post(small_url1, data=json.dumps(small_body), headers={'Content-Type': 'application/json'})
            print(response.json())
    return '无绑定openid'


@shared_task
def update_stock_merchant_table(data):
    id = data['id']
    history = AliDiscountCouponMerchantTableHistory.objects.get(id=id)
    history.add_state = 2
    history.save()
    excel_data = []
    excel_data_title = ['商户号']
    excel_data.append(excel_data_title)
    today = datetime.datetime.now()
    try:
        redis_conn = get_redis_connection("cache")
        check_business_no_list_server = redis_conn.get("check_business_no_list")
        if check_business_no_list_server:
            check_business_no_list = json.loads(check_business_no_list_server.decode())
        else:
            check_business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
            redis_conn.setex("check_business_no_list", 86400, json.dumps(check_business_no_list))
        remark = ''
        if history.data_type == 1:
            file_contents = requests.get(history.file.url).content
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)
            table = wb.sheets()[0]
            title = table.row_values(0)
            nrows = table.nrows
            business_no_index = title.index('商户号')
            business_no_list = []
            for i in range(1, nrows):
                rowValues = table.row_values(i)
                business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
                if business_no:
                    business_no_list.append(business_no)
        else:
            business_no_list = json.loads(history.business_list)
        try:
            blace_sub_mchid_list = []
            for business_no in business_no_list:
                if not business_no.isdigit():
                    remark += f'{business_no}商户号错误,'
                    continue
                if len(business_no) != 10:
                    remark += f'{business_no}商户号错误,'
                    continue
                if not business_no.startswith('10') and not business_no.startswith('60'):
                    remark += f'{business_no}商户号错误,'
                    continue
                if business_no not in check_business_no_list:
                    check_merchant = Merchant.objects.filter(wyfMerchantID=business_no).first()
                    if check_merchant:
                        continue
                    business_no_list.append(business_no)
                    md5 = hashlib.md5()
                    md5.update(str(business_no).encode('utf-8'))
                    sign = md5.hexdigest()
                    url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
                    body = {
                        'account': 'gdyouzheng',
                        'shopId': sign
                    }
                    reaponse = requests.post(url=url, data=body)
                    try:
                        reaponse = reaponse.json()
                    except:
                        remark += f'{business_no}商户号添加失败,'
                        continue
                    if 'wx_channel_merchant_id' not in reaponse:
                        remark += f'{business_no}商户号错误,'
                        continue
                    if reaponse['wx_channel_merchant_id'] == '':
                        remark += f'{business_no}获取商户信息失败,'
                        continue
                    with transaction.atomic():
                        # 创建事务保存点
                        save_id = transaction.savepoint()
                        try:
                            merchant_data = {}
                            channel_mch_id = reaponse['channel_mch_id']
                            channel = Channel.objects.filter(mchid=channel_mch_id).first()
                            if channel:
                                merchant_data['channel_id'] = channel.id
                            merchant_data['name'] = reaponse['shopName']
                            merchant_data['short_name'] = reaponse['shopNickname']
                            merchant_data['wyfMerchantID'] = business_no
                            merchant_data['ruiyinxinMerchantID'] = sign
                            merchant_data['create_time'] = datetime.datetime.now()
                            if reaponse['businessType'] == '1':
                                merchant_data['subject_type'] = 3
                            elif reaponse['businessType'] == '2':
                                merchant_data['subject_type'] = 2
                            elif reaponse['businessType'] == '3':
                                merchant_data['subject_type'] = 1
                            else:
                                merchant_data['subject_type'] = 4
                            level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                            if level:
                                merchant_data['level_id'] = level.id

                            merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone']})
                            merchant_data['merchantuser_id'] = merchantuser.id
                            merchant_data['account_name'] = reaponse['cardName']
                            merchant_data['account_bank'] = reaponse['bankName']
                            merchant_data['account_number'] = reaponse['card']
                            if 'latitude' in reaponse:
                                merchant_data['latitude'] = reaponse['latitude']
                            if 'longitude' in reaponse:
                                merchant_data['longitude'] = reaponse['longitude']
                            card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                            card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                            if card_copy_url:
                                merchant_data['card_copy'] = card_copy
                            merchant_data['identification_number'] = reaponse['identity']
                            merchant_data['store_address'] = reaponse['address']
                            store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                            store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                            if store_header_copy_url:
                                merchant_data['store_header_copy'] = store_header_copy
                            merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data, wyfMerchantID=str(business_no))
                            if not merchant.managerName:
                                merchant.managerName = reaponse['keeper']
                            if not merchant.managermobile:
                                merchant.managermobile = reaponse['phone']
                            if not merchant.prov_name:
                                merchant.prov_name = reaponse['province']
                                merchant.city_name = reaponse['city']
                                merchant.district_name = reaponse['area']
                            if not merchant.business_category:
                                merchant.business_category_id = 1
                            if not merchant.store_logo_copy:
                                merchant.store_logo_copy = 'default_merchant_logo.jpg'
                            merchant.save()
                            if cereate and merchant.channel:
                                store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                                if store_header_copy_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                                store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                                if store_indoor_copy_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                                merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                                merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                                if merchantCheck_url:
                                    MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                            merchantchannel_data = {'merchant_id': merchant.id}
                            if reaponse['allchannelJson']['channel_ruiyinxin']:
                                if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                                    merchantchannel_data['channel_id'] = 2
                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                          merchant_id=merchant.id,
                                                                          channel_id=merchantchannel_data['channel_id'])
                            if reaponse['allchannelJson']['channel_sxf_tq']:
                                if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                                    merchantchannel_data['channel_id'] = 3
                                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data,
                                                                          merchant_id=merchant.id,
                                                                          channel_id=merchantchannel_data['channel_id'])
                            transaction.savepoint_commit(save_id)
                        except Exception as e:
                            print(e)
                            remark += f'{business_no}添加商户信息失败,'
                            continue
            sub_mchid_list = list(MerchantChannel.objects.filter(merchant__wyfMerchantID__in=business_no_list).values_list('sub_mchid', flat=True).distinct())
            for sub_mchid in sub_mchid_list:
                merchant_table = AliDiscountCouponMerchantTable.objects.filter(sub_mchid=sub_mchid).first()
                if merchant_table:
                    if history.table_type == 1:
                        if merchant_table.is_white != 1:
                            merchant_table.is_white = 1
                            merchant_table.white_create_time = today
                            merchant_table.save()
                    else:
                        if merchant_table.is_black != 1:
                            if merchant_table.is_white != 1:
                                merchant_table.is_black = 1
                                merchant_table.black_create_time = today
                                merchant_table.save()
                                blace_sub_mchid_list.append(sub_mchid)
                            else:
                                merchant_table.is_black = 1
                                merchant_table.black_create_time = today
                                merchant_table.save()
                else:
                    merchant_table_data = {}
                    merchant_table_data['sub_mchid'] = sub_mchid
                    if history.table_type == 1:
                        merchant_table_data['is_white'] = 1
                        merchant_table_data['white_create_time'] = today
                    else:
                        merchant_table_data['is_black'] = 1
                        merchant_table_data['black_create_time'] = today
                        blace_sub_mchid_list.append(sub_mchid)
                    AliDiscountCouponMerchantTable.objects.update_or_create(defaults=merchant_table_data, sub_mchid=sub_mchid)
            conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                   port=settings.DATABASES['default']['PORT'],
                                   database=settings.DATABASES['default']['NAME'],
                                   user=settings.DATABASES['default']['USER'],
                                   password=settings.DATABASES['default']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            sql = 'UPDATE tb_ali_discount_coupon_merchant_table,(SELECT * FROM (SELECT merchant_id,sub_mchid FROM tb_merchant_channel WHERE sub_mchid IN (SELECT sub_mchid FROM tb_ali_discount_coupon_merchant_table WHERE merchant_id IS NULL))AS a LEFT JOIN (SELECT id,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c SET tb_ali_discount_coupon_merchant_table.merchant_id=c.merchant_id,tb_ali_discount_coupon_merchant_table.business_no=c.wyfMerchantID WHERE tb_ali_discount_coupon_merchant_table.merchant_id IS NULL AND tb_ali_discount_coupon_merchant_table.sub_mchid=c.sub_mchid;'
            try:
                cursor.execute(sql)
                conn.commit()
            except:
                conn.rollback()
            finally:
                cursor.close()
                conn.close()
            alidiscountcoupon_id_list = list(AliDiscountCouponMerchant.objects.filter(sub_mchid__in=blace_sub_mchid_list, alidiscountcoupon__publish_end_time__gte=today).values_list('alidiscountcoupon_id', flat=True).distinct())
            for alidiscountcoupon_id in alidiscountcoupon_id_list:
                alidiscountcoupon = AliDiscountCoupon.objects.filter(id=alidiscountcoupon_id).first()
                if alidiscountcoupon.platform_id == 13:
                    remark += f'{alidiscountcoupon.template_id}四川批次未实现此功能,'
                    continue
                cookies = alidiscountcoupon.platform
                headers = {'Cookie': cookies.cookie}
                query_set = blace_sub_mchid_list
                slice_begin = 0
                slice_end = 400
                while True:
                    query_set1 = query_set[slice_begin: slice_end]
                    if not query_set1:
                        break
                    completemodifyinfo = ''
                    modifymchinfo = 'Del_'
                    seqcode = int(time.time()*1000)
                    for query in query_set1:
                        excel_data_body = []
                        completemodifyinfo += f'({query}),'
                        modifymchinfo += f'{query},'
                        excel_data_body.append(str(alidiscountcoupon.template_id))
                        excel_data_body.append(str(query))
                        excel_data_body.append(str(seqcode))
                        excel_data.append(excel_data_body)
                    check_password_url = 'https://pay.weixin.qq.com/index.php/core/risk_ctrl/verifyPasswd?g_ty=ajax'
                    check_password_body = {
                        "ecc_csrf_token": cookies.ecc_csrf_token,
                        "passwd": cookies.token,
                        "sec_factor": "1"
                    }
                    check_password_response = requests.post(url=check_password_url, data=check_password_body, headers=headers)
                    try:
                        check_password_data = json.loads(check_password_response.content)
                    except:
                        remark = 'Cookie已失效,请联系管理员!'
                        break
                    if check_password_data['errorcode'] != 0:
                        remark = '操作密码错误!请联系管理员'
                        break
                    credential = check_password_data['data']['credential']
                    submit_url = f'https://pay.weixin.qq.com/xdc/mktbatchdetails/index.php/extend/mktaccount/BudgetRevise?passwd={credential}&g_ty=ajax'
                    # remark += f'申请单:{seqcode},'
                    submit_body = {
                        "stockid": alidiscountcoupon.template_id,
                        "seqcode": seqcode,
                        "modifytype": "2",
                        "ecc_csrf_token": cookies.ecc_csrf_token,
                        "completemodifyinfo": f"|{completemodifyinfo}",
                        "modifymchinfo": f"Add_|{modifymchinfo}"
                    }
                    submit_response = requests.post(url=submit_url, data=submit_body, headers=headers)
                    try:
                        submit_data = json.loads(submit_response.content)
                    except:
                        remark = 'Cookie已失效,请联系管理员!'
                        break
                    print(submit_data)
                    if 'errcode' not in submit_data:
                        if submit_data['errorcode'] != 0:
                            remark += f'{alidiscountcoupon.template_id}批次状态有误:{submit_data["msg"]}'
                            slice_begin += 400
                            slice_end += 400
                            continue
                    time.sleep(10)
                    a = 1
                    while True:
                        if a > 40:
                            break
                        query_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/query_unprocess_task?g_ty=ajax'
                        query_body = {
                            "page": "1",
                            "workflow_id": "10",
                            "business_code": seqcode,
                            "ecc_csrf_token": cookies.ecc_csrf_token,
                            "cert_id": "0",
                            "order_type": "0",
                            "process_state": "0"
                        }
                        query_response = requests.post(url=query_url, data=query_body, headers=headers)
                        print(query_response.json())
                        if query_response.json()['data']:
                            agree_url = f'https://pay.weixin.qq.com/index.php/extend/audittask/pass_process?g_ty=ajax'
                            agree_body = {
                                "ecc_csrf_token": cookies.ecc_csrf_token,
                                "process_id": query_response.json()['data']['list'][0]['process_id'],
                            }
                            agree_response = requests.post(url=agree_url, data=agree_body, headers=headers)
                            time.sleep(6)
                            break
                        time.sleep(10)
                        a += 1
                    slice_begin += 400
                    slice_end += 400
                AliDiscountCouponMerchant.objects.filter(sub_mchid__in=blace_sub_mchid_list, alidiscountcoupon_id=alidiscountcoupon_id).delete()
                remark += f'{alidiscountcoupon.template_id}批次中含有黑名单商户已删除,'
            book_name_xlsx = history.file.name.split('.')[0] + '_日志.xlsx'
            sheet_name_xlsx = 'json1'
            file_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/{book_name_xlsx}')
            write_excel_xlsx(file_path, sheet_name_xlsx, excel_data)
            with open(file_path, 'rb') as recode_f:
                file_contents = recode_f.read()
            file = pic_upload(file_contents, book_name_xlsx)
            # remark += f'申请单链接:https://circle.sutpay.cn/{file}'
            os.remove(file_path)
            history.add_state = 3
            history.remark = remark
            history.logs_file = file
            history.save()
            re_history = AliDiscountCouponMerchantTableHistory.objects.filter(add_state=1).first()
            if re_history:
                update_stock_merchant_table.delay({'id': re_history.id})
            return '添加成功'
        except Exception as e:
            print(e)
            history.add_state = 4
            history.save()
            re_history = AliDiscountCouponMerchantTableHistory.objects.filter(add_state=1).first()
            if re_history:
                update_stock_merchant_table.delay({'id': re_history.id})
            return '失败'
    except Exception as e:
        print(e)
        history.add_state = 4
        history.save()
        re_history = AliDiscountCouponMerchantTableHistory.objects.filter(add_state=1).first()
        if re_history:
            update_stock_merchant_table.delay({'id': re_history.id})
        return '失败'



if __name__ == '__main__':
    pass
    # queryset = AliDiscountCoupon.objects.filter(coupon_channel=2, last_update_time__isnull=True)
    # # queryset = AliDiscountCoupon.objects.filter(coupon_channel=2, id=140)
    # for query in queryset:
    #     sync_activity_merchant({'id': query.id})

    # sync_activity_merchant({'id': 38228})
    # update_stock_usable_merchant({'id': 1914})
    # for i in [27807]:
    #     print(i)
    #     sync_activity_wyh_data({'record_id': i})
    # print(sync_activity_wyh_relevance())
    # synchronization_settlementrecord({"start": "2024-04-01", "end": "2024-04-08", "merchant_id": "10077", 'user_id': 1})
    update_stock_merchant_table({'id': 25})
